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.

 

[c_m_s_jr]

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).

There is one error in my comment above. It actually starts on the second time period (^2) because the % resulted are 0.03, 0.05, and 0.02. NOT 0.3, 0.5, and 0.2. By starting on the second time period the NPV evaluates as:

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

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

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

Sorry for the oversight. Hope things are still clear.

 

[taurean]

By the way, if you have 2007 or later then the formula can be reduced to (it probably also means that someone wrote it in 2003 or older version days):
=NPV(-0.9,,IFERROR((MID(A1,256-COLUMN(A:IV),1)%),""))

 

[pgc01]

Clever use of NPV to extract the digits out of a string!

I think it's easier to read with Row() instead of Column, like:

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

Not robust, though. one could replace the Row(), like:

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

or, non-volatile:

=NPV(-0.9,,IFERROR(MID(A1,20-ROW(INDEX(A:A,1):INDEX(A:A,20)),1)%,""))

 

[Domski]

Well sussed guys I was intrigued but didn't have any time today to evaluate it. Definitely one for the locker!

Dom

 

[taurean]

Clever use of NPV to extract the digits out of a string!

I think it's easier to read with Row() instead of Column, like:

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

Not robust, though. one could replace the Row(), like:

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

or, non-volatile:

=NPV(-0.9,,IFERROR(MID(A1,20-ROW(INDEX(A:A,1):INDEX(A:A,20)),1)%,""))

Probably, it was written from the perspective that the formula would be copied *down* and not across so the column references used would make the formula stable.

And this will take into account the exact length of string.
=NPV(-0.9,,IFERROR((MID(A1,LEN(A1&" ")-ROW($A$1:INDEX(A:A,LEN(A1),1)),1)%),""))

 

[pgc01]

Probably, it was written from the perspective that the formula would be copied *down* and not across so the column references used would make the formula stable.

Good point. It makes perfect sense.

I prefer, however, to use Row(), it's easier to read. Your formula and my last 2 formulas can be copied down with no problem.

Remark:

In your formula

= ...-ROW($A$1:INDEX(...

makes the formula not robust. If the user inserts rows before row 1 the formula no longer works.

= ...-ROW(INDEX(A:A,1):INDEX(...

is preferable, as it's indifferent to insertions/deletions of rows.

 

[Jas_Foster]

Good point. It makes perfect sense.

I prefer, however, to use Row(), it's easier to read. Your formula and my last 2 formulas can be copied down with no problem.

Remark:

In your formula

= ...-ROW($A$1:INDEX(...

makes the formula not robust. If the user inserts rows before row 1 the formula no longer works.

= ...-ROW(INDEX(A:A,1):INDEX(...

is preferable, as it's indifferent to insertions/deletions of rows.

I realize that these are older posts. However, has anyone noticed that the formula does not convert negative numbers?

For example:

-€ 9,35 converts to 9.35

<tbody>
</tbody>