Alpha and Numeric Strings have " ' " as their leading character

[Hollando]

Hello all,

I hope for this to be my last inquiry on this topic for the immediate future.

I have columns A-F which are general formatted cells. These cells have Alpha and Numeric strings. In each of these strings is an '. Does anyone have a non-VBA solution in order for to be rid of the leading character in question?


Please note I have tried to find ' and replace with (blank), but unfortunately this only works when the ' is not the leading character. As always your help and guidance no matter how small would be greatly appreciated.

With regards to all of my inquiries thus far I would like to thank each and everyone of you who have passed along a solution to the problems I was encountering.

Thanks,

Hollando

 

[BenMiller]

Try highlighting the column with the problem, and go to Data>Text To Columns and click through to FINISH.

 

[Hollando]

Try highlighting the column with the problem, and go to Data>Text To Columns and click through to FINISH.

Unfortunately, no. This only works with Blank Cells and Numeric (as it will change the cell format).

 

[JoeMo]

If the ' character is always the leading charcter and doesn't appear elsewhere in the string and you have a string like:
'abc def ghi
in A1,
then in B1:
=SUBSTITUTE(A1,"'","")
copy down to cover your data then Copy column B entries, Paste Special - Values in column A and delete column B.

 

[Hollando]

If the ' character is always the leading charcter and doesn't appear elsewhere in the string and you have a string like:
'abc def ghi
in A1,
then in B1:
=SUBSTITUTE(A1,"'","")
copy down to cover your data then Copy column B entries, Paste Special - Values in column A and delete column B.

Unfortunately no...Won't grab the ' at the begining of the string.

 

[JoeMo]

Unfortunately no...Won't grab the ' at the begining of the string.

Works for me. Are your strings imported?

 

[Hollando]

Works for me. Are your strings imported?

Yes they are. If you are referring to the fact that they are an extract from our SAP ERP system.

Hollando

 

[Rick Rothstein]

Yes they are. If you are referring to the fact that they are an extract from our SAP ERP system.

Are you sure that leading character is an apostrophe? Select one of the cells with that character as the first characters and look at the cell itself, not the Formula Bar... do you see the apostrophe or not?

 

[Hollando]

Are you sure that leading character is an apostrophe? Select one of the cells with that character as the first characters and look at the cell itself, not the Formula Bar... do you see the apostrophe or not?

Yes it's definitely an apostrophe. Just to be certain I copied the character and tried to seach for it, but still couldn't be located.

No in the cell without double clicking or pressing F2 I cannot see the apostrophe.