Wednesday, October 27, 2004

Convert Text to Values and Values to Text

Converting text to values is easy using the VALUE function.
I generally insert a temporary column next to the column of text and create a formula =VALUE(A1) where A1 is the first cell in the range to change.
Fill the formula down.
Now select the formula range (eg Column B) and Copy then Paste Special as Values over the original cells.
Finally delete the temporary column.

Converting values back to text can be done a couple of ways.
For simple numbers in one cell you can use the CONCATENATE function but only concatenate the one cell. Eg =CONCATENATE(A1)
This however causes a problem if you want to maintain special formats, Eg Percents, Dates etc.
The way to convert these is to use the TEXT function.
Eg =TEXT(A1,"dd-mm-yyyy") will turn a date in cell a1 to text in the target cell with the format of dd-mm-yyyy. (For an explanatioin of date and time formats see this previous article)
The tip here is to use any valid format inside the quotes. You can determine the syntax of a valid format by looking inside the Format - Cells - Number - Custom dialog box.


Posted by: Jethro | 2:12 AM |