Apostrophe In Data Downloaded

[Tiziano]

I am importing into Excel a text file where one particular column has some
records with an apostrophe ("'") in front of them. Not all the records in
that column have the apostrophe in front. The apostrophe acts as a
formatting feature as it actually does not visually appear in the cell.
Only by pressing F2 could one see that the apostrophe is there. (This is
probably a quirk created by the ERP system where I get the data from.)

How can I get rid of the apostrophe? I tried using the LEFT function and
capturing everything starting from the second character, but it does not
work...

Thanks.

 

[Guest]

Insert and run this small macro:

Sub tic_killer()
For Each r In ActiveSheet.UsedRange
If r.PrefixCharacter = "'" Then
r.Value = r.Value
End If
Next
End Sub

If you are not familiar with VBA see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Tiziano]

Thank you for your suggestion!
Just out of curiosity, is there a way to do it via a function rather than a
macro?

 

[David McRitchie]

A formula can only return a value, so it cannot change
the formula or content. So the answer to your question
is NO. For the difference between
formulas and macros see Chip Pearson's
Macros And Functions
http://www.cpearson.com/excel/differen.htm

 

[David]

Tiziano wrote

Just out of curiosity, is there a way to do it via a function rather
than a macro?

In an empty cell put a 1, Edit|Copy, select all cells with apostrophies,
Edit|PasteSpecial > Multiply

 

[vitruvian]

I am importing into Excel a text file where one particular column has some
records with an apostrophe ("'") in front of them. Not all the records in
that column have the apostrophe in front. The apostrophe acts as a
formatting feature as it actually does not visually appear in the cell.
Only by pressing F2 could one see that the apostrophe is there. (This is
probably a quirk created by the ERP system where I get the data from.)

How can I get rid of the apostrophe? I tried using the LEFT function and
capturing everything starting from the second character, but it does not
work...

Thanks.

Hey Tiz,

I had the same problem once before

Lets assume the value is in cell A1, you can use =RIGHT(A1,LEN(A1)-1)

LEN will calculate the length, and then you can use that minus the one
to get rid of the leading apostrophe

Some manual checking might be required from time to time to ensure
that the formula's will always work for you and go all the way down
the range you are using

Hope this helps you

 

[JMay]

If either in A1 I have '123 or 'abc
If I enter (in B1) =Len(A1) I get 3 (the ' is not considered in the
count).
If dealing with numbers only I'd just use (in B1) =A1 + 0