Leading Apostrophe

[Phil-T]

I was sent an Excel file and opened with my Excel 2003. And in every field,
the data is prefaced with an apostrophe. When looking at the cell you can't
see it and the apostrophe doesn't show up in print. Where is this coming
from and how do I get rid of it.

 

[Irishrich]

The leading apostrophe is there to designate the data as text rather than a
number. Best way of getting rid of it is to highlight column and use Text to
Columns tool in the data menu. Once you are in simply click finish and the
data should now be without the apostrophe.

 

[Phil-T]

Tried that on cloumn A and it didn't change anything.

 

[Ms-Exl-Learner]

All the below function will clear the leading Apostrophe

=TRIM(A1)
=CLEAN(A1)
=SUBSTITUTE(A1,"'","")

Remember to Click Yes, if this post helps!

 

[Irishrich]

Try this

Use the same wizard. In step 1 choose 'Delimited'. In step 2 tick in the box
marked other and in the space to the right of that type in single apostrophe.
When you hit next to step 3 there should be only one column available and
when you hit finish all values should now appear without the apostrophe. I
believe it only works on numbers. Do you have actual text values showing in
the data?

 

[Phil-T]

If I clicked on A1, and typed any of the functions shown below, it just
created a circular reference and blanked out the entire cell rather than just
eliminating the leading apostrophe.

 

[Sheeloo]

If your data is in Sheet1 then go to Sheet2 and in cell A1 enter
=TRIM(Sheet1!A1)
Then drag the corners of cell A1 on Sheet2 to cover the required area to get
all the data cells you have... Then you can press Ctrl-A, right-click, choose
paste special and then choose Values...

You don't need to do anything as ' before text does not impact anything...

 

[Irishrich]

Type formula in B1 referencing A1 which will return your values in column B.
To get a clean column of raw data highlight column B and use copy & paste
values to get rid of formula.

 

[Ms-Exl-Learner]

Ok no need to use that functions also I found a simple method for your query.

Just select the cells in which you are having the leading Apostrophe's and
do copy and in a new worksheet place the cursor where do you want to paste
the values without Apostrophe now do right click and you can see Paste
Special Option (Note the Paste Special will get enable after doing copy only)
Select Paste Special and select Values and give Ok. That's It.

Remember to Click Yes, if this post helps!

 

[Ms-Exl-Learner]

The reason for suggesting the paste special is your post seems that you are
not familiar of using the formula's. That is the reason I skipped to an
another alternate solution.

Remember to Click Yes, if this post helps!

 

[David Biddulph]

Don't put it in A1, as that's what gives a circular reference; put it in
another cell.

 

[Gord Dibben]

One more guess.

If you change the cell format to Center-Aligned does the apostrophe change
to a Caret ^

Tools>Options>Transition.

Make sure "Transistion Navigation Keys" is unchecked.


Gord Dibben MS Excel MVP

 

[Lori Miller]

Edit > Clear > Formats should work,
or Copy and Paste formats from another cell.

You may need to reapply other formats after however.
There doesn't seem to be a way of removing this attribute
independently as the .prefixcharacter property is read-only.

 

[Phil-T]

Yeah!!! The EDIT / CLEAR / FORMATS did it!

Thanks to all for your efforts. Have never used this group before, but it
won't be the last time.

 

[Sheeloo]

Pl mark the post as answered so that others can benefit from it.