Removing Letters and Dashes

[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&"01­23456789"))),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.

 

[L. Howard Kittle]

You probably did not give enough examples of your data, but if it is this
single format of A22k55-77, then try this in C1

=MID(A1,2,2)&MID(A1,5,2)&RIGHT(A1,2)

Returns 225577

then use =LEFT(C1,4) in D1

Returns 2255

For the 555-55-5555 select and under Edit > Find > - (a dash) > Replace >
(leave blank) > OK

HTH
Regards,
Howard

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&"01­23456789"))),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.

 

[SanCarlosCyclist]

You probably did not give enough examples of your data, but if it is this
single format of A22k55-77, then try this in C1

=MID(A1,2,2)&MID(A1,5,2)&RIGHT(A1,2)

Returns 225577

then use =LEFT(C1,4) in D1

Returns 2255

For the 555-55-5555 select and under Edit > Find > - (a dash) > Replace >
(leave blank) > OK

HTH
Regards,
Howard


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&"01­23456789"))),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.

Howard, thanks for your response. I would use your suggestion if the
letter-number-dash was always the same. I am looking for a formula
that will work no matter how many numbers or letters I have in a given
cell, the results will always report on the 3 scenarios above. When
they are constant, the solution is easy by using Left, Mid, or Right
in the formula. I often have inconsistent data.

 

[L. Howard Kittle]

Thought that may be the case, I am at a loss for a solution given the broad
scope of the data you mention.

Regards,
Howard

You probably did not give enough examples of your data, but if it is this
single format of A22k55-77, then try this in C1

=MID(A1,2,2)&MID(A1,5,2)&RIGHT(A1,2)

Returns 225577

then use =LEFT(C1,4) in D1

Returns 2255

For the 555-55-5555 select and under Edit > Find > - (a dash) > Replace >
(leave blank) > OK

HTH
Regards,
Howard


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&"01­23456789"))),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.

Howard, thanks for your response. I would use your suggestion if the
letter-number-dash was always the same. I am looking for a formula
that will work no matter how many numbers or letters I have in a given
cell, the results will always report on the 3 scenarios above. When
they are constant, the solution is easy by using Left, Mid, or Right
in the formula. I often have inconsistent data.

 

[Ron Rosenfeld]

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&"01­23456789"))),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.

Easy to do with a User Defined Function (UDF).

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=NumsOnly(A1) (for all the digits)

or

=NumsOnly(A1,n) (for the first "n" digits)

in some cell.

The function returns the digits as a string in order to preserve any leading
zero's, and also to return a blank if there are no digits in the string.

If this is not required, you may want to make some modifications.


===============================
Option Explicit
Function NumsOnly(s As String, Optional DigitCount As Variant) As String
Dim re As Object
Dim sNums As String
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D"
sNums = re.Replace(s, "")
If IsMissing(DigitCount) Then
NumsOnly = sNums
Else
NumsOnly = Left(sNums, DigitCount)
End If
End Function
===============================
--ron

 

[Rick Rothstein]

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&"01­23456789"))),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.