Numbers that are really not numbers?

[C Brandt]

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

One way:

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

 

[C Brandt]

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

 

[Guest]

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

 

[bplumhoff]

--text

Regards,
Bernd

 

[Niek Otten]

Hi Craig,

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

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

--
Kind regards,

Niek Otten

| 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
|
| | > 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 <[email protected]>,
| >
| > > 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
|
|

 

[Guest]

haw does that 1 works (--text) ?


"(e-mail address removed)" skrev:

 

[bplumhoff]

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

 

[RagDyeR]

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

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

 

[Guest]

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


"(e-mail address removed)" skrev:

 

[bplumhoff]

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