Converting lists of numbers

E

Eliot

When a list of numbers has been imported into Excel with an ' in front of the
first character (i.e. '0439333222), how can you convert that list so that the
' is removed?

Example: to convert '0439333222 to 0439333222

?

Any quick tips would be appreciated. Thanks.
 
W

Wiley

The ' is used to force the cell to a text format. If you remove it, the
displpay will likey drop the leading 0's unless it is formatted as a text
cell.

Having said that, you can remove it by using Find/Replace. Find ', and
leave the Replace box blank.
 
G

Gord Dibben

The apostrophe indicates that the "number" is imported as text which it must in
order to retain the leading 0.

You can get rid of the apostrophe but then you will lose the leading 0.

Although you could format to Custom 0000000000 to show a leading 0

To get rid of the apostrophe try this.............

Format all cells to General.

Copy an empty cell and select you range of numbers.

Paste Special>Add>OK>Esc

OR

Data Text to columns>Next>Next>Column Data Format>General>Finish.


Gord Dibben MS Excel MVP
 
Top