Formula for removing repeating symbol

[Guest]

Hello,
I have a large data base forwarded to me in Excel. The creator of the db has
included an apostrophe and a space before the name of each company and at the
beginning of the address in the following column. For example:
' The International Assoc. of Birdwatchers

It would appear to me that the apostrophe as the effect of shortening the
text in the cells so that you can only see the first word or two.

Can anyone tell me what this is and how can I remove it from the database
without going through them one at a time?

Thanks!

 

[SusanV]

Assuming there is no other place in the data in that field where you have an
apostrophe followed by a space (such as "the two dogs' tails") you could use
the Replace function.

 

[RayV]

Hello,
I have a large data base forwarded to me in Excel. The creator of the db has
included an apostrophe and a space before the name of each company and at the
beginning of the address in the following column. For example:
' The International Assoc. of Birdwatchers

It would appear to me that the apostrophe as the effect of shortening the
text in the cells so that you can only see the first word or two.

Can anyone tell me what this is and how can I remove it from the database
without going through them one at a time?

Thanks!

Insert a column in Excel and add this formula
=RIGHT(A1,LEN(A1)-1)
where A1 is the cell with the offending apostrophe+space
copy this formula all the way down to the end of the data
select the entire new column then Edit - Copy
then Edit - Paste Special
select Values then click OK
you will end up with a 'clean' column

Now you can delete the column with the offending text. If deleting
the original column could cause reference problems you can Paste
Special right onto the original data and then delete the column you
made with the formulas.

 

[fredg]

Hello,
I have a large data base forwarded to me in Excel. The creator of the db has
included an apostrophe and a space before the name of each company and at the
beginning of the address in the following column. For example:
' The International Assoc. of Birdwatchers

It would appear to me that the apostrophe as the effect of shortening the
text in the cells so that you can only see the first word or two.

Can anyone tell me what this is and how can I remove it from the database
without going through them one at a time?

Thanks!

If there might be apostrophe's elsewhere in the data, you could use:

Update YourTable Set YourTable.[Fieldname] = IIf(Left([FieldName],2) =
"' ",Mid(FieldName],3),[FieldName])