How to remove apostrophee ' from the begining of a cell ?

[GN0001]

Data has come to me as: '00001234 and contact name as 'John.

I need to remove the apostrophe or ' from the beginning of my text.

I tried Right, Len, code and substitute, clean, trim function and they didn't work.

I formatted the column as a text....

any help?

GGGGG

 

[aamirsq]

Did you try Ctrl+F. search & replace ' (apostrophe) with none then enter.


Should do the job w/o any formula !

 

[GN0001]

Yes, I did, it didn't work.

However I found the answer.

=TRIM(SUBSTITUTE(A1, CHAR(160), " "))

But I am looking for easier solution, because each column of the worksheet has ' sign. I have to go to each column one by one to remove this character. It is time consuming.

Do you have any other solution?

Thanks,

GGGGG

 

[shrivallabha]

You can do text to columns.


1. Select the column.

2. Choose "Text to columns"

3. Choose "Delimited"

4. Deselect / un-check all delimiting options.

5. Finish text to columns.


If this works for you then you can record macro for this and loop through all columns!

 

[GN0001]

Hello Shrivallabha,

I did this, but it didn't work.

I went to clear format, the apostrophe was taken away. However one solution doesn't work in all scenarios.

Regards,

GGGGG

 

[GN0001]

Hello Shrivallabha,

what you say didn't work. I used clear format and it took all the apostrophe sign out.

But this solution may not work for all the cases. Thank you for the help and your time.

GGGGG

 

[shrivallabha]

Please post a sample workbook. Then all of us can look at various cases that you have.

 

[DaveTurton]

you can try

[pre]

Sub RemoveApostrophes()
With Worksheets("Sheet1").UsedRange
.Value = .Value
End With
End Sub

[/pre]

 

[DaveTurton]

Changed sheet1 to activesheet as I don't know the name of your sheet

[pre]

Sub RemoveApostrophes()
With ActiveSheet.UsedRange
.Value = .Value
End With
End Sub

[/pre]