S
SanCarlosCyclist
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.
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.