Can't convert text

  • Thread starter Thread starter George W. Barrowcliff
  • Start date Start date
G

George W. Barrowcliff

Excel 2007

Imported my phone bill from Cingular

Number of minutes column contains data type of 2 (Text) but Value()
gives #VALUE error. Using the formula wizard, the evaluation shows
correct number.

What gives?

Column E is the minutes column
E1 has 15
=Type(E1) gives answer of 2
=Value(E1) gives #VALUE
using the TYPE wizard and entering E1 shows evaluation of = " 15" and
return integer of 2.
using the VALUE wizard and entering E1 shows evluation of = " 15"

TIA
 
You probably have non-visible junk characters in the cell. For example, if
In cell A14 enter:
=" 10 "
then =VALUE(A14) will show 10

but if A14 has something like:
=" 10 " & CHAR(160)

we get the error message.
 
I agree with Gary you have text in the field. The Type function returns 1 if
the value is a number and 2 if the value is text.

You can convert this in a helper column with the formula:
=VALUE(TRIM(A4))

assuming that the value you are inspecting is in A4.

The formula values can then copied over the original using Paste Special,
Values. Or you can try converting these inplace using a macro:

Press ALT + F11, Insert, Module and paste the code below into the module.

Return to the worksheet (ALT + Q). Select the values to change and press ALT
+ F8, select the macro from the list and Click Run.

Sub test()
For Each c In Selection
If Not IsEmpty(c) Then
c.Value = Trim(c) * 1
End If
Next
End Sub

If you are not happy with the results close the book without saving, or
better still create a backup and work with this.

Peter Atherton
 
If you have downloaded the data from a website you often get the non-
breaking space character (with a code of 160), and the TRIM function
will not remove this. However, you can remove it with Find/Replace -
highlight the cells, then do CTRL-H:

Find what: Alt-0160
Replace with: leave blank
click Replace All

where Alt-0160 means hold down the Alt key while typing 0160 on the
numeric keypad.

Hope this helps.

Pete
 
Back
Top