Parse Numerics

[MWilliams]

I have a column of data where each one has a varying number of letters and numbers.

ACB-LCEG253
ACB-4840
ACB-PDG27451
PDG27499
PDG27422-01
PDG27422-02

etc.

I would like to create another column with a formula that shows just the numeric portion of each cell.

Any suggestions would be appreciated.

 

[MWilliams]

I love this!
I only wish this was a standard file property so we could sort files based on the numeric portion of the filenames!

Thanks again!

 

[c_m_s_jr]

Another way, maybe not as clever as what sweep put in:

=(MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890))+1))

Does NOT need to be confirmed with CTRL+SHIFT+ENTER

 

[MWilliams]

Another nice one, Jr.!

 

[Joe4]

Interesting note:

Davesweep's reply returns a numeric entry.
c_m_s_jr reply returns a text entry.

So, if you had any entry where the first number is a zero, the numeric one will drop the leading zero, while the second one will not (the second one also keeps the dash).

Not sure which one suits your needs better, but it is good to know both options and how they differ so you can make that determination.

 

[MWilliams]

Interesting note:

Davesweep's reply returns a numeric entry.
c_m_s_jr reply returns a text entry.

So, if you had any entry where the first number is a zero, the numeric one will drop the leading zero, while the second one will not (the second one also keeps the dash).

Not sure which one suits your needs better, but it is good to know both options and how they differ so you can make that determination.

Thanks for pointing that out. I noticed this as well, but hadn't yet noticed that the second version would leave the dash, which I don't NEED, but is definitely nice to have.

When I use DaveSweep's, it formats to currency.

 

[Domski]

Dom,

I'm ashamed to say that I pinched it from somewhere and stored it for later use during some googling.

LOL I've done that before and been asked for an explanation I couldn't provide

I'm guessing some clever soul with a more in depth knowledge of what the formula does in the background worked it out as a trick you can perform with NPV.

Dom

 

[Joe4]

When I use DaveSweep's, it formats to currency.

Mine did the same thing intitially. The NPV function is a financial function, so that may be the default. However, if you change the format of your cells to number, it should correct that.

 

[c_m_s_jr]

Another way, maybe not as clever as what sweep put in:

=(MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890))+1))

Does NOT need to be confirmed with CTRL+SHIFT+ENTER

Revised to remove dash, but still formats as text to keep any leading zero.

SUBSTITUTE((MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890))+1)),"-","")

If you want to flip to a number, just add "+0" at the end of the formula. This addition will of course remove the leading zero

 

[MWilliams]

Nice

 

[c_m_s_jr]

Hi,

This array might work for you:

=NPV(-0.9,,IF(ISERR(MID(A1,256-COLUMN(A:IV),1)%),"",MID(A1,256-COLUMN(A:IV),1)%))

After some refresher on how a NPV calculation works I think I figured out how this formula is working.

The:

IF(ISERR(MID(A1,256-COLUMN(A:IV),1)%),"",MID(A1,256-COLUMN(A:IV),1)%))

Will return a blank or any nurmeric value it finds as a percent. In addition it will reverse the order which is important for the NPV portion of the formulas.

In the first example of the posters data, the string results to {"","","",0.03,0.05,0.02,"","","","","","",""}).

NPV (Net Present Value) actual math formula is:

SUM(Cashflow/(1+rate)^period)

In the formula above:
Rate = -0.9
Cash flow is the numeric value returned by IF(ISERR...)
Period = the position of the number (0.3 = 1, 0.5 = 2, 0.2 = 3)

The NPV for 0.3 would look like: 0.3/(1+-0.9)^1 = 3

The NPV for 0.5 would look like: 0.5/(1+-0.9)^2 = 50

The NPV for 0.2 would look like: 0.2/(1+-0.9)^3 = 200

When the NPV sums all of these together you get 253 which is the number the poster was looking for.

Pretty cool trick. The biggest key is that it puts the numbers in the reverse order so they will be in the correct place (1's, 10's, 100's, 1000's, etc).