I have a column with 2 alpha letters leading numbers and also numbers only in
the same column. I want to put all records in a new column without any
alpha. How would I do that?

Example:

ar3456789
bt56789
234567
78901234
etc.

Hi,


=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000))))

All 1 line drag down as required

Mike

"rciolkosz" wrote:

I have a column with 2 alpha letters leading numbers and also numbers only in
the same column. I want to put all records in a new column without any
alpha. How would I do that?

Example:

ar3456789
bt56789
234567
78901234
etc.

Copy/paste this UDF to a general module in your workbook.

Function RemAlpha(str As String) As String
'Remove Alphas from a string
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D"
RemAlpha = re.Replace(str, "")
End Function

In a helper column enter

=RemAlpha(cellref)

Drag/copy down.


Gord Dibben MS Excel MVP

On Fri, 18 Sep 2009 11:53:01 -0700, rciolkosz
wrote:

I have a column with 2 alpha letters leading numbers and also numbers only in
the same column. I want to put all records in a new column without any
alpha. How would I do that?

Example:

ar3456789
bt56789
234567
78901234
etc.

Tik Num Num Only
rc65789876 0
dl987898
768765544 987898
234876
234876

It returned above. It appears it only did every other line.



"Mike H" wrote:

Hi,


=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000))))

All 1 line drag down as required

Mike

"rciolkosz" wrote:

I have a column with 2 alpha letters leading numbers and also numbers only in
the same column. I want to put all records in a new column without any
alpha. How would I do that?

Example:

ar3456789
bt56789
234567
78901234
etc.

This works well but is skipping every other line.

"Mike H" wrote:

Hi,


=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000))))

All 1 line drag down as required

Mike

"rciolkosz" wrote:

I have a column with 2 alpha letters leading numbers and also numbers only in
the same column. I want to put all records in a new column without any
alpha. How would I do that?

Example:

ar3456789
bt56789
234567
78901234
etc.

It looks to me like you entered the formula in b2, but kept the a1 reference
in the formula.

Responders don't know what cell addresses to use, unless you tell them
specifically where your data is. If you don't identify the location, then
they can only show a sample, and typically use a1. Change the a1 in the
formula to the correct address of the first cell you want to check (likely
a2). Then copy it down.

Regards,
Fred.

"rciolkosz" wrote in message
...
Tik Num Num Only
rc65789876 0
dl987898
768765544 987898
234876
234876

It returned above. It appears it only did every other line.



"Mike H" wrote:

Hi,


=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000))))

All 1 line drag down as required

Mike

"rciolkosz" wrote:

I have a column with 2 alpha letters leading numbers and also numbers
only in
the same column. I want to put all records in a new column without any
alpha. How would I do that?

Example:

ar3456789
bt56789
234567
78901234
etc.

On Fri, 18 Sep 2009 12:16:05 -0700, rciolkosz
wrote:

It returned above. It appears it only did every other line.

Mike's formula assumed your first entry as in A1. If you copied it, but did
not change the cell references appropriately, you might have see strange
results. Although I don't understand the "every other line" issue, nor the
fact, from what you show above, that you did not extract the entire number.

Please copy/paste the formula that you are using; and copy/paste the results.

Also, there may be something about the cell formatting that is throwing things
off.
--ron