Numbers that are really not numbers?
 

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

Numbers that are really not numbers?
 

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

Numbers that are really not numbers?
 

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

Numbers that are really not numbers?
 

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

Numbers that are really not numbers?
 

--text

Regards,
Bernd

Numbers that are really not numbers?
 

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

Numbers that are really not numbers?
 

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


" skrev:

--text

Regards,
Bernd

Numbers that are really not numbers?
 

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

Numbers that are really not numbers?
 

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

Numbers that are really not numbers?
 

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

Numbers that are really not numbers?
 

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