Sorting numbers with ' preceding it

  • Thread starter Thread starter DrB
  • Start date Start date
D

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 '?
 
Copy an empty cell, select all the data and do Edit / Paste Special / Add. This
should coerce the data back to numeric.
 
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.
 
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.
 
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
 
Back
Top