cannot convert text string into value

[Andy]

Dear all,

I can convert a text string "12345" into the value 12,345.00 by using
Value(text).
However when I copy a text string "12345" from the monthly e-bill statement
and using value(text) to convert it, it only returns a #value! error.
How can I convert that string into value?

thanks


Andy

 

[Pete_UK]

Check the string more closely - you may have a non-breaking space
character at the end, in which case you could use:

=VALUE(LEFT(A1,LEN(A1)-1))

or

=VALUE(RIGHT(A1,LEN(A1)-1))

if it's only a single space (and depending on if it is at the start or
the end).

Hope this helps.

Pete

 

[David Biddulph]

You've probably got non-printing characters as well as your string 12345.
Get rid of those extra characters.

 

[Andy]

thank you Pete but i can sure no space at all.


Andy



"Pete_UK" <[email protected]>
???????:8e26d3d6-73e7-4cd1-8828-379f24f37812@k13g2000hse.googlegroups.com...
Check the string more closely - you may have a non-breaking space
character at the end, in which case you could use:

=VALUE(LEFT(A1,LEN(A1)-1))

or

=VALUE(RIGHT(A1,LEN(A1)-1))

if it's only a single space (and depending on if it is at the start or
the end).

Hope this helps.

Pete

 

[Andy]

I really can't find any extra character, this is not the case as you
mention.
thanks David


Andy

 

[Dave Peterson]

I'd look again.

Chip Pearson has a very nice addin that will help determine what those cells
really contain:
http://www.cpearson.com/excel/CellView.aspx

 

[Don Guillett]

Try this
Sub fixmynums()
Application.ScreenUpdating = False
On Error Resume Next
For Each C In Selection 'Range("a1:q" & lr)
If Trim(Len(C)) > 0 And C.HasFormula = False Then
C.NumberFormat = "General"
C.Value = CDbl(C)
End If
Next

Application.ScreenUpdating = True
End Sub

 

[Pete_UK]

Obviously, spaces are difficult to see !! <g>

If your text number is in A1, put this formula somewhere:

=LEN(A1)

to see how many characters are actually in that cell. Is it the same
as how many you think there are?

Another thing to try is to select the cell and then to click in the
extreme right of the formula bar as if to edit the cell, and see where
the cursor ends up - it might be a space away from your last digit.

Hope this helps.

Pete

 

[Ron Rosenfeld]

Dear all,

I can convert a text string "12345" into the value 12,345.00 by using
Value(text).
However when I copy a text string "12345" from the monthly e-bill statement
and using value(text) to convert it, it only returns a #value! error.
How can I convert that string into value?

thanks


Andy

You have "invisible" characters in the string.

Try this to process the string into a number:

A1: your_text_string

=--SUBSTITUTE(TRIM(A1),CHAR(160),"") or
=--SUBSTITUTE(CLEAN(A1),CHAR(160),"")



--ron

 

[Andy]

Oh my god~
you are right!

Thanks indeed


Andy



"Pete_UK" <[email protected]>
???????:1858edbd-2288-4197-bb36-cc2ba5b22ba3@x35g2000hsb.googlegroups.com...
Obviously, spaces are difficult to see !! <g>

If your text number is in A1, put this formula somewhere:

=LEN(A1)

to see how many characters are actually in that cell. Is it the same
as how many you think there are?

Another thing to try is to select the cell and then to click in the
extreme right of the formula bar as if to edit the cell, and see where
the cursor ends up - it might be a space away from your last digit.

Hope this helps.

Pete

 

[Andy]

Thanks Don, but it is too complicated for me

Andy

 

[Andy]

Your formula still doesn't work as I find there is a space at the rightmost
of the string, how can I eliminate it by formula?

thanks


Andy

 

[Pete_UK]

You're welcome, Andy - thanks for feeding back.

Pete

 

[Ron Rosenfeld]

Your formula still doesn't work as I find there is a space at the rightmost
of the string, how can I eliminate it by formula?

thanks

Usually, that space at the right is char(160). Since it isn't, you will have
to determine what the character is, and then use the SUBSTITUTE function to
remove it.

Try:

=CODE(RIGHT(A1,1))

That will return a number. Substitute that number for the "160" in the formula
I gave you.
--ron

 

[Don Guillett]

If desired, send your UNCHANGED workbook to my address below along with what
you want.

 

[Pete_UK]

Or you can use the LEFT formula I gave you this morning - change the 1
to a 2 if you have 2 of these "spaces", etc.

Pete

 

[Andy]

My problem has been solved using the LEFT formula.
Thank you PETE, Ron, Don, Dave and David as well.


Andy


"Pete_UK" <[email protected]>
???????:7bb4b710-2120-4927-92a0-e053f0513a57@y38g2000hsy.googlegroups.com...
Or you can use the LEFT formula I gave you this morning - change the 1
to a 2 if you have 2 of these "spaces", etc.

Pete