Replace Function

[Lisa W.]

I am trying to replace a single letter in a field, but with in several cells
may be a different letter. I can successfully remove one letter but if I try
to create a formula to replace for example, "a", replace "s", etc... I cannot
get this to work.

Below is an example of the data elements:

a4000
c4011
d2011

I need to get rid of the letter that preceeds the series of numbers.

Can someone please assist me with creating a formula/function that will
remove the letters.

Thanks,
Lisa W.

 

[Fred]

Hello Lisa,

You are going to have to start with defining (for yourself, and [you may
have dones so already) telling us) exactly what you want to replace and under
what conditions. Is it simply (always) the first character of the field?

 

[Lisa W.]

Hello Fred,

Yes I want to replace the first character of every field. It's just that
the first character could be the letter "a", "s", "d", or "c".

Thank You!
Lisa W.

 

[fredg]

Hello Fred,

Yes I want to replace the first character of every field. It's just that
the first character could be the letter "a", "s", "d", or "c".

Thank You!
Lisa W.

Hello Lisa,

You are going to have to start with defining (for yourself, and [you may
have dones so already) telling us) exactly what you want to replace and under
what conditions. Is it simply (always) the first character of the field?

You don't get extra points for brevity. You may know what you want but
we can't see your computer nor read your mind.
It's still not clear. Where are you doing this? in a Query, on a Form,
in a Report?
Do you wish to permanently change the table field's data, or just
display the existing data differently on a form or report?

To just display the data....
To always remove the first character (whatever that character is) in a
Select query:
NewColumn:Mid([FieldName],2)

or to remove the first characters only if it is one of the 4 mentioned
above, in a query:

NewColumn:IIf(Left([Fieldname],1) In ("a","s","d","c"),
Mid([FieldName],2),[FieldName])

To Remove that first character ONLY if it is one of the 4 you
mentioned above, using the Replace function, (in a query):
NewColumn:Replace(Replace(Replace(Replace(Replace([FieldName],"a",""),"s","")"d",""),"c","")

Note the above Replace() functions will remove those characters
anywhere in the field, not just the first ones.

All the above would be used in a Select query and then [NewColumn]
would be shown on the form or in the report.

To permanently change the field's data you would use the above
expressions in an Update query.

For Example ...
Update YourTable Set YourTable.[FieldName] = Mid([FieldName],2) Where
Left([FieldName],1) In ("a","s","d","c")

 

[Lisa W.]

Thank you! I will use in a query.

Apologies for not giving you full details...:-(

Hello Fred,

Yes I want to replace the first character of every field. It's just that
the first character could be the letter "a", "s", "d", or "c".

Thank You!
Lisa W.

Hello Lisa,

You are going to have to start with defining (for yourself, and [you may
have dones so already) telling us) exactly what you want to replace and under
what conditions. Is it simply (always) the first character of the field?

You don't get extra points for brevity. You may know what you want but
we can't see your computer nor read your mind.
It's still not clear. Where are you doing this? in a Query, on a Form,
in a Report?
Do you wish to permanently change the table field's data, or just
display the existing data differently on a form or report?

To just display the data....
To always remove the first character (whatever that character is) in a
Select query:
NewColumn:Mid([FieldName],2)

or to remove the first characters only if it is one of the 4 mentioned
above, in a query:

NewColumn:IIf(Left([Fieldname],1) In ("a","s","d","c"),
Mid([FieldName],2),[FieldName])

To Remove that first character ONLY if it is one of the 4 you
mentioned above, using the Replace function, (in a query):
NewColumn:Replace(Replace(Replace(Replace(Replace([FieldName],"a",""),"s","")"d",""),"c","")

Note the above Replace() functions will remove those characters
anywhere in the field, not just the first ones.

All the above would be used in a Select query and then [NewColumn]
would be shown on the form or in the report.

To permanently change the field's data you would use the above
expressions in an Update query.

For Example ...
Update YourTable Set YourTable.[FieldName] = Mid([FieldName],2) Where
Left([FieldName],1) In ("a","s","d","c")

 

[John Spencer]

Permanently or only temporarily

Temporary (calculated field)
Field: StripIt: IIF([MyField] like "[A-Z]*",Mid([MyField],2),[MyField])

A permanent change could use an update query that looked like the following

UPDATE [MyTable]
SET [MyField] = Mid([MyField])
WHERE [MyField] like "[A-Z]*"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County