format of cells from database

**DaveRust** · 05-11-2009, 03:56 PM

Hi All,

Scenario:
-I have an excel export from a database.
-The export has multiple columns of data.
-The column I am interested consists of numbers and some text.
-The numbers are preceeded by "zeros", e.g. "00054" or "0021"
-The text is usually "N/A"

Problem:
-The column cells are formatted as "General".
-I need to get the "00054" to just "54"
-I am looking for a solution other than re-writing the column by hand (hundreds of column entries)

I've Tried:
-Changing column format to "Number", but the "00054" format remains.
-Played around with other cell format types
-If I double click the cell, the entry then goes to the format I need!!! e.g. "00054" -- double click-- "54". Wallaaaaa... I will get carpal tunnel if I have to double click all these columns entries!!

Once again, thanks in advance for your help!! You guys are a great resource!!!

DaveRust

**Chance2** · 05-11-2009, 04:12 PM

You could use the Data --> Text To Columns option (just highlight the one column and click through).

Or add code to select the column and use...

Please Login or Register  to view this content.

**malnahar** · 05-11-2009, 04:28 PM

Hi,

To work out the current exported data, in I20 use code =1*H20, this will remove the zeros preceeding the numbers but still you have to find another way for the cells that do not contain numbers

Regards

**DaveRust** · 05-13-2009, 01:09 PM

malnahar,

thanks, I found that if I place '1' in a cell, then copy special and click multiply to the whole column, it works just fine.

Thanks for the input