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" 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

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


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

On Sep 1, 2:34 pm, "Andy" 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

Obviously, spaces are difficult to see !! <g

If your text number is in A1, put this formula somewhe

=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" wrote:
thank you Pete but i can sure no space at all.

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

On Sep 1, 2:34 pm, "Andy" 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 -

Oh my god~
you are right!

Thanks indeed


Andy



"Pete_UK"
...
Obviously, spaces are difficult to see !! <g

If your text number is in A1, put this formula somewhe

=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" wrote:
thank you Pete but i can sure no space at all.

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

On Sep 1, 2:34 pm, "Andy" 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" wrote:
Oh my god~
*you are right!

Thanks indeed

Andy

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

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

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

"Andy" wrote in message
...
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

"Andy" wrote in message
...
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

Thanks Don, but it is too complicated for me

Andy



"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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Andy" wrote in message
...
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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Andy" wrote in message
...
Thanks Don, but it is too complicated for me

Andy



"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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Andy" wrote in message
...
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" 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

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"
...
On Mon, 1 Sep 2008 21:34:24 +0800, "Andy" 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

On Tue, 2 Sep 2008 03:52:56 +0800, "Andy" 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

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" 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

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


Andy


"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

On Sep 1, 8:52 pm, "Andy" 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