Spent about 30 minutes today fixing a column of numbers that I needed to act
like numbers, not text.

479862673



479862673

The first number above has a character in front of it that prevents it from
being treated like a number. If I were to multiply it by one I get the
#VALUE! error. I deleted the first character and then it acts like a number.

I finaly figured out that a RIGHT(text,9) will solve my problem.

Is there a simpler solution?

Thanks in advance,
Craig

One way:

Copy an empty cell. Select your "text numbers". Choose Edit/Paste
Special, selecting the Values and Add radio buttons. Click OK.

In article ,
"C Brandt" wrote:

Spent about 30 minutes today fixing a column of numbers that I needed to act
like numbers, not text.

479862673



479862673

The first number above has a character in front of it that prevents it from
being treated like a number. If I were to multiply it by one I get the
#VALUE! error. I deleted the first character and then it acts like a number.

I finaly figured out that a RIGHT(text,9) will solve my problem.

Is there a simpler solution?

Thanks in advance,
Craig

JE:
Thanks for the suggestion. Tried it but it didn't work for me. The problem
turns out to be that pesky first non-printable character. I thought
CLEAN(A1) would work(A1 contains the number/test), it didn't. Eventually I
tried the RIGHT(A1,9) but the first attempt at this, I miscounted the
digits and entered RIGHT(A1,10). I was in a deadline crisis mode so I
manually deleted the first character of around 200 entries, twice. It was in
later research that I stubbled onto my error and used the correct RIGHT
formula.

Thanks for the response. I can live with the RIGHT function, just
disapointed that the CLEAN function didn't do the job.

Regards,

Craig

"JE McGimpsey" wrote in message
...
One way:

Copy an empty cell. Select your "text numbers". Choose Edit/Paste
Special, selecting the Values and Add radio buttons. Click OK.

In article ,
"C Brandt" wrote:

Spent about 30 minutes today fixing a column of numbers that I needed to
act
like numbers, not text.

479862673



479862673

The first number above has a character in front of it that prevents it
from
being treated like a number. If I were to multiply it by one I get the
#VALUE! error. I deleted the first character and then it acts like a
number.

I finaly figured out that a RIGHT(text,9) will solve my problem.

Is there a simpler solution?

Thanks in advance,
Craig

Hi Craig,

Use David McRitchie's TRIMALL() function which can be downloaded he

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

--
Kind regards,

Niek Otten

"C Brandt" wrote in message ...
| JE:
| Thanks for the suggestion. Tried it but it didn't work for me. The problem
| turns out to be that pesky first non-printable character. I thought
| CLEAN(A1) would work(A1 contains the number/test), it didn't. Eventually I
| tried the RIGHT(A1,9) but the first attempt at this, I miscounted the
| digits and entered RIGHT(A1,10). I was in a deadline crisis mode so I
| manually deleted the first character of around 200 entries, twice. It was in
| later research that I stubbled onto my error and used the correct RIGHT
| formula.
|
| Thanks for the response. I can live with the RIGHT function, just
| disapointed that the CLEAN function didn't do the job.
|
| Regards,
|
| Craig
|
| "JE McGimpsey" wrote in message
| ...
| One way:
|
| Copy an empty cell. Select your "text numbers". Choose Edit/Paste
| Special, selecting the Values and Add radio buttons. Click OK.
|
| In article ,
| "C Brandt" wrote:
|
| Spent about 30 minutes today fixing a column of numbers that I needed to
| act
| like numbers, not text.
|
| 479862673
|
|
|
| 479862673
|
| The first number above has a character in front of it that prevents it
| from
| being treated like a number. If I were to multiply it by one I get the
| #VALUE! error. I deleted the first character and then it acts like a
| number.
|
| I finaly figured out that a RIGHT(text,9) will solve my problem.
|
| Is there a simpler solution?
|
| Thanks in advance,
| Craig
|
|

The easiest way to convert text numbers that are preceded with an apostrophe
to be XL recognized *true* numbers, and accomplish this 'en masse', is to
use TTC (Text to Columns).

Select the column of "bad" numbers,
Then simply open and close TTC.

<Data <TextToColumns <Finish

And you're done!

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"C Brandt" wrote in message
...
JE:
Thanks for the suggestion. Tried it but it didn't work for me. The problem
turns out to be that pesky first non-printable character. I thought
CLEAN(A1) would work(A1 contains the number/test), it didn't. Eventually I
tried the RIGHT(A1,9) but the first attempt at this, I miscounted the
digits and entered RIGHT(A1,10). I was in a deadline crisis mode so I
manually deleted the first character of around 200 entries, twice. It was in
later research that I stubbled onto my error and used the correct RIGHT
formula.

Thanks for the response. I can live with the RIGHT function, just
disapointed that the CLEAN function didn't do the job.

Regards,

Craig

"JE McGimpsey" wrote in message
...
One way:

Copy an empty cell. Select your "text numbers". Choose Edit/Paste
Special, selecting the Values and Add radio buttons. Click OK.

In article ,
"C Brandt" wrote:

Spent about 30 minutes today fixing a column of numbers that I needed to
act
like numbers, not text.

479862673



479862673

The first number above has a character in front of it that prevents it
from
being treated like a number. If I were to multiply it by one I get the
#VALUE! error. I deleted the first character and then it acts like a
number.

I finaly figured out that a RIGHT(text,9) will solve my problem.

Is there a simpler solution?

Thanks in advance,
Craig

nope no Excel-function for that (i wonder too :-) )
a VBA macro can do it, if u vant it just say

--text

Regards,
Bernd

haw does that 1 works (--text) ? :-)


" skrev:

--text

Regards,
Bernd

Hello,

--" 123" is the number 123.

Or if you have ' 123 in cell A1 then --A1 will give you the number 123
again.

HTH,
Bernd

oh ok, was hoping that it could fix fx. a123 to 123


" skrev:

Hello,

--" 123" is the number 123.

Or if you have ' 123 in cell A1 then --A1 will give you the number 123
again.

HTH,
Bernd

Hello,

Then take this UDF:
Function XtractNum(s As String) As Double
Dim i As Long, d As Double, c As String, f As Double

i = 0: d = 0#: f = 1#

StateStart:
i = i + 1
If i Len(s) Then GoTo StateEnd
c = Mid(s, i, 1)
Select Case c
Case "0" To "9", "."
d = CDbl(c)
GoTo StatePreComma
Case Else
GoTo StateStart
End Select

StatePreComma:
i = i + 1
If i Len(s) Then GoTo StateEnd
c = Mid(s, i, 1)
Select Case c
Case "0" To "9"
d = 10# * d + CDbl(c)
GoTo StatePreComma
Case "."
GoTo StatePostComma
Case Else
GoTo StateEnd
End Select

StatePostComma:
i = i + 1
If i Len(s) Then GoTo StateEnd
c = Mid(s, i, 1)
Select Case c
Case "0" To "9"
f = f / 10#
d = d + CDbl(c) * f
GoTo StatePostComma
Case Else
GoTo StateEnd
End Select

StateEnd:
XtractNum = d

End Function

HTH,
Bernd