|  | Can't convert text | | |
| | | George W. Barrowcliff |  |
|  Posted: Fri Aug 15, 2008 11:55 pm Post subject: Can't convert text |  |
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 |
| |
| | | Gary''s Student | |
| | Posted: Sat Aug 16, 2008 7:46 am Post subject: RE: Can't convert text | |
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.
--
Gary''s Student - gsnu2007k
"George W. Barrowcliff" wrote:
| Quote: | 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
|
|
| |
| | | Billy Liddel | |
| | Posted: Sun Aug 17, 2008 11:00 am Post subject: RE: Can't convert text | |
| |  | |
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
"Gary''s Student" wrote:
| Quote: | 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.
--
Gary''s Student - gsnu2007k
"George W. Barrowcliff" wrote:
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
|
|
| |
| | | Pete_UK | |
| | Posted: Sun Aug 17, 2008 4:41 pm Post subject: Re: Can't convert text | |
| | | |
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
On Aug 16, 2:55 am, "George W. Barrowcliff"
<george.barrowcl...@flash.net> wrote:
| Quote: | 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 |
|
| |
|
|
