Sorting numbers with ' preceding it

[DrB]

Have data from another source which are addresses including zip code
numbers. For some reason there is an ' in from of all the data so when I
replace or correct some and do not put the ' in front they do not sort
correctly. Tried to run "replace" but the ' is not recognized. Anyone know
how to either replace all the data with nothing in place of the '?

 

[Ken Wright]

Copy an empty cell, select all the data and do Edit / Paste Special / Add. This
should coerce the data back to numeric.

 

[Tom Ogilvy]

This will convert all your zip codes to numbers, but you will lose the
leading zeros on all zip codes that lead with zeros - which is probably why
they all start with a single quote. You can format the cells as 00000 to
show the leading zeros, but if you are going to export the data as CSV or
something, the leading zeros will again be lost. You need to think through
what you are going to do.

 

[DrB]

I copied a blank cell and then highlighted the data cells with the '
preceding the numbers and special pasted all) with add checked. It worked.

Do not have any zip with zeros preceding it so it looks like this way works.
Thank you.

 

[Ken Wright]

Cheers Tom - I should have clarified that.

 

[JMay]

With your zip in A10 as '00157
In B10 =TEXT(A10,"00000") wil produce 00157 << w/o the " ' " But
a formula, so:
Copy B10 and Paste-Special Value (from B10 to B10 (to itself)).
HTH