Please help me understand / amend a complex formula! (To extract only the numbers from a cell of mixed data).

[supersezzie]

Hi,

I'm new here so hello everyone! I'm hoping someone can help me...

I've got a spreadsheet set up that in one cell I needed to extract just the numerical data with no spaces or punctuation from a previous cell, which could also have text, spaces and punctuation.

I tried for hours and eventually found a formula online that I copied into the cell (I'll paste it at the bottom), which worked except for one problem: it omits the 0's if they are at the start of the number. It's really important that the 0's stay in.

For example:

SE0619 comes out as 619.
However, my next step is to split this number and look it up in a look up table so I would actually need to be looking up '06' as the code.

Can anyone understand this formula enough to tell me how to edit it to keep in the 0's as I don't think it's a straightforward formatting of the cell issue, I think it's something embedded in the formula?

Failing that, can anyone at least talk me through what the formula is doing so I can have a go at fixing it myself? Any help would be gratefully received (this is going to help a conservation charity so you'd be doing your bit...)!!!

Sezzie

Here is the formula:

=SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,Seq,1))*Seq,0),Seq)+1,1)*10^Seq/10)

Seq is defined as: =ROW(INDEX('Sheet1'!$1:$65536,1,1):INDEX('Sheet1'!$1:$65536,255,1))

 

[SteveO59L]

Try posting a sample of your data, and what you would like to do with it

 

[supersezzie]

Thanks Steve....

My data is grid references that can be written in a number of ways, eg:

SU5467
ST32,46
NT0815
QP 0003 4475

It can have any number from 2-10 digits after the reference. I need to then extract the numbers only in the form exactly as they are written, for example, from the above, I would want:

5467
3246
0815
00034475

But with the formula and formatting as it is, I am getting

5467
3246
815
34475

Once I have the numbers extracted, I have all the other formulae in place to do the rest, which is converting it from one type of grid refernce to another.

Do you need any more info? Thanks for helping folks