Converting text to numbers, but not blank cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Anika V. Bristalli - 15 Aug 2007 18:16 GMT

Hi, I've got a large survey data-set in Excel 2003. Some values are
pure text, some are numbers but were recorded as text. There are also
many blanks in each collumn.
So, I am trying to convert the numbers from text format to a number
format. I know the basic tricks (=VALUE(), or Type 1, Copy, Paste
Special--> Multiply + Value). The only problem is that all of them
convert a blank into a 0. I really need to keep the blanks as blanks.
Does anyone have a trick or a macro that would make sure that blanks
remain as blanks when converting text to number? Thank you!

Reply to this Message

Peo Sjoblom - 15 Aug 2007 18:41 GMT

If you copy an empty cell, select the range and do edit>paste special and
select add it will not change blank cells to 0 but it will change any text
number. Another good trick is to select the column/range and do data>text to
columns and click finish immediately

Signature

Regards,

Peo Sjoblom

> Hi, I've got a large survey data-set in Excel 2003. Some values are
> pure text, some are numbers but were recorded as text. There are also
[quoted text clipped - 5 lines]
> Does anyone have a trick or a macro that would make sure that blanks
> remain as blanks when converting text to number? Thank you!

Reply to this Message

Anika V. Bristalli - 15 Aug 2007 21:02 GMT

The first option works great - thank you!!!

> If you copy an empty cell, select the range and do edit>paste special and
> select add it will not change blank cells to 0 but it will change any text
[quoted text clipped - 17 lines]
>
> - Show quoted text -

Reply to this Message

Rate this thread: