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

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...@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've probably got non-printing characters as well as your string 12345.
Get rid of those extra characters.
--
David Biddulph

"Andy" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>

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


Andy



"Pete_UK" <(E-Mail Removed)>
???????:8e26d3d6-73e7-4cd1-8828-(E-Mail Removed)...
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...@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

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:(E-Mail Removed)...
> You've probably got non-printing characters as well as your string 12345.
> Get rid of those extra characters.
> --
> David Biddulph
>
> "Andy" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>>
>
>

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:(E-Mail Removed)...
> > You've probably got non-printing characters as well as your string 12345.
> > Get rid of those extra characters.
> > --
> > David Biddulph
> >
> > "Andy" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> 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

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
(E-Mail Removed)
"Andy" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>

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...@gamil.com> wrote:
> thank you Pete but i can sure no space at all.
>
> Andy
>
> "Pete_UK" <pashu...@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...@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 -

On Mon, 1 Sep 2008 21:34:24 +0800, "Andy" <(E-Mail Removed)> 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

Oh my god~
you are right!

Thanks indeed


Andy



"Pete_UK" <(E-Mail Removed)>
???????:1858edbd-2288-4197-bb36-(E-Mail Removed)...
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...@gamil.com> wrote:
> thank you Pete but i can sure no space at all.
>
> Andy
>
> "Pete_UK" <pashu...@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...@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 -