Here are your answers (make sure you read the part about using

Ctrl+Alt+Enter to commit the first two formulas, not just Enter by

itself)...

[1] Posted previously by Lars-Åke Aspelin...

=MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&A1,ROW($1:$300),1)),ROW($1:$300))+1,1),10^(300-ROW($1:$300))),2,300)

This is an array formula and has to be confirmed with CTRL+SHIFT+ENTER

rather than just ENTER.

It has the following (known) limitations:

- The input string in cell A1 must be shorter than 300 characters

- There must be at most 14 digits in the input string.

(Following digits will be shown as zeroes.)

Maybe of no practical use, but it will also handle the following two cases

correctly:

- a "0" as the first digit in the input will be shown correctly in the

output

- an input without any digits at all will give the empty string as output

(rather than 0).

[2] All the same conditions/limitations from #1 above apply...

=MID(SUMPRODUCT(--MID("01"&LEFT(A1,FIND("-",A1&"-")),SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&LEFT(A1,FIND("-",A1&"-")),ROW($1:$300),1)),ROW($1:$300))+1,1),10^(300-ROW($1:$300))),2,300)

[3] =SUBSTITUTE(A1,"-","")

--

Rick (MVP - Excel)

I learned here from one of you geniuses a nifty trick of how to

display only a number when there are also letters in the cell.

I learned that if I have in cell A1 the value of “P100C” and, I type

in another cell, =LOOKUP(9.9E+307,--LEFT(MID(A1,--LEFT(MIN(FIND

({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))),LEN(A1)),ROW($1:$99))), the

result will be 100.

I would also love to be able to do the following:

1) If cell A1 is “A22k55-77”, I would like the formula in another cell

to return the value 225577 (all the numbers in cell A1)

2) If cell A1 is “A22k55-77”, I would like the formula in another cell

to return the value of 2255 (Specific numbers from cell A1-Example 1st

4 numbers)

3) If cell A1 is 555-55-5555, I would like the formula in another cell

to return the value of 555555555 (removing dashes from a social

security number)

If possible, I’d appreciate if you would post a formula for each of

the above scenarios.

I very much appreciate the help that you all provide us. It is

invaluable.