cannot convert text string into value

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

"Andy" <Andy_1011.TakeThisOut@gamil.com> wrote in message
news:OPLR6dDDJHA.3432@TK2MSFTNGP05.phx.gbl...
> 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
>
>

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1.TakeThisOut@austin.rr.com
"Andy" <Andy_1011.TakeThisOut@gamil.com> wrote in message
news:OPLR6dDDJHA.3432@TK2MSFTNGP05.phx.gbl...
> 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
>
>

On Mon, 1 Sep 2008 21:34:24 +0800, "Andy" <Andy_1011.DeleteThis@gamil.com> wrote:

>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

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


On Sep 1, 3:55 pm, "Andy" <Andy_1....DeleteThis@gamil.com> wrote:
> thank you Pete but i can sure no space at all.
>
> Andy
>
> "Pete_UK" <pashu....DeleteThis@auditel.net>
> ???????:8e26d3d6-73e7-4cd1-8828-379f24f37...@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
>
> On Sep 1, 2:34 pm, "Andy" <Andy_1....DeleteThis@gamil.com> wrote:
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -

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

Pete

On Sep 1, 8:47 pm, "Andy" <Andy_1....TakeThisOut@gamil.com> wrote:
> Oh my god~
>  you are right!
>
> Thanks indeed
>
> Andy
>

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

On Sep 1, 8:52 pm, "Andy" <Andy_1....DeleteThis@gamil.com> wrote:
> 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
>