cannot convert text string into value

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

Andy wrote:
>
> I really can't find any extra character, this is not the case as you
> mention.
> thanks David
>
> Andy
>
> "David Biddulph" <groups [at] biddulph.org.uk> ¼¶¼g©ó¶l¥ó·s»D:48bbfa57_1@glkas0286.greenlnk.net...
> > 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
> >>
> >>
> >
> >

--

Dave Peterson

Oh my god~
you are right!

Thanks indeed


Andy



"Pete_UK" <pashurst.DeleteThis@auditel.net>
???????: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


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 -

Thanks Don, but it is too complicated for me

Andy



"Don Guillett" <dguillett1.DeleteThis@austin.rr.com>
???????:%23o%23TFYEDJHA.5656@TK2MSFTNGP04.phx.gbl...
> 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.DeleteThis@austin.rr.com
> "Andy" <Andy_1011.DeleteThis@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
>>
>>
>

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


"Ron Rosenfeld" <ronrosenfeld.TakeThisOut@nospam.org>
???????:21bob41atts64sfs6484himp041srglg3c@4ax.com...
> On Mon, 1 Sep 2008 21:34:24 +0800, "Andy" <Andy_1011.TakeThisOut@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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1.TakeThisOut@austin.rr.com
"Andy" <Andy_1011.TakeThisOut@gamil.com> wrote in message
news:%23lxLquGDJHA.3432@TK2MSFTNGP05.phx.gbl...
> Thanks Don, but it is too complicated for me
>
> Andy
>
>
>
> "Don Guillett" <dguillett1.TakeThisOut@austin.rr.com>
> ???????:%23o%23TFYEDJHA.5656@TK2MSFTNGP04.phx.gbl...
>> 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 Tue, 2 Sep 2008 03:52:56 +0800, "Andy" <Andy_1011 RemoveThis @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

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