# Extracting Number from text Data W/ leading zero

#### gazcore

##### New Member
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

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

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

##### New Member
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

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

##### New Member
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"

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:
Code:
``````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

##### Well-known Member
Give this a try:

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

Replies
16
Views
150
Replies
6
Views
84
Replies
7
Views
402
Replies
2
Views
48
Replies
16
Views
437

1,195,662
Messages
6,010,999
Members
441,579
Latest member
satishrazdhan

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back