Extracting Number from text Data W/ leading zero

[gazcore]

Hello,

I am working on a project and i am extracting data the has numerical values through out one cell. Most of these contain a leading zero at the beginning. I am using the following formula below which works great, except it excludes the leading zero. I need the leading zero to be included. Any ideas?

=SUM(MID(0&A33,LARGE(ISNUMBER(--MID(A33,ROW(INDIRECT("1:"&LEN(A33))),1))*ROW(INDIRECT("1:"&LEN(A33))),ROW(INDIRECT("1:"&LEN(A33))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A33)))/10)

as this is an Array formula you must use ctrl+shift+enter

 

[Joe4]

Welcome to the Board!

If you are just trying to extract a number, what purpose does the SUM function serve?
You only use SUM when you are trying to add a bunch of different values from various ranges together. Adding is a mathematical operation and will convert any text entries to numeric, and leading zeroes really have no meaning with numeric sums (hence, they are dropped).

 

[gazcore]

To be honest this is a formula i found in a previous thread. I modified it slightly for my needs, but ran into this one issue. I have not seen an another solution. if you have any other ideas that would help i am all ears.

 

[Joe4]

Can you provide a link to this other thread?
Also, can you describe your exact problem? There may be other ways of going about it if we know what your data looks like and what you are trying to do.

 

[gazcore]

ADVERTISEMENT

Do my data looks something like this

"0333 Advertising 60030 Control Center 600".

A text to columns does not work very well as there are several different locations for each numerical value and spaces. if i could get it all into one numerical string (includeding the leading zero) i can seperate it out with LEFT and MID formulas for what i need. the end result would look something like this "033360030600"


Here is the link

http://www.mrexcel.com/forum/excel-questions/443983-extract-only-numbers-text-string.html

 

[Joe4]

I think I would opt for a variation of the VBA solution in post 45 of that thread here: http://www.mrexcel.com/forum/excel-...t-only-numbers-text-string-5.html#post4039942

So, this is that UDF, modified slightly:

Function RemoveAlpha(cellInput) As String
    Set cellInput = Intersect(cellInput.Parent.UsedRange, cellInput)
    
    For i = 1 To Len(cellInput)
        Select Case Mid(cellInput, i, 1)
            Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 0
                Charval = Mid(cellInput, i, 1)
            Case Else
                Charval = ""
        End Select
    RemoveAlpha = RemoveAlpha & Charval
    Next i
        
End Function

So, if your string is in cell A1, here is the formula you would use to return what you want:
=RemoveAlpha(A1)

 

[István Hirsch]

Give this a try:

=MID(NPV(-0.9,,IFERROR((MID("1"&A1,LEN("1"&A1&" ")-ROW(INDIRECT("1:"&LEN("1"&A1))),1)%),"")),2,20)