Extracting 7 digit number from alphanumeric string

[snowball]

Hi,

I'm trying to extract a 7 digit number from an alphanumeric string (in Excel
2003)when the 7 digit number can appear anywhere in the string and the string
can also contain other numbers which I do not want. I am currently using the
formula
=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$500),1)),0),COUNT(1*MID(A1,ROW($1:$500),1)))
which works fine when the alphanumeric string only contains the 7 digit
number I want and no other numbers. However I get errors when the string
includes other numbers.

Is there a formula I could use which would extract the 7 digit number but
which would omit all other groups of, for example, 2 digits or 10 digits?

e.g. in the string "text 1 abc1234567trial10nfy" I would like to extract
1234567.

Many thanks for any help you can give.
snowball

 

[Joel]

simpley add a LEN() function. Something like this

=if(len(my formula)=7,my formula,"")


=if(len(1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$500),1)),0),COUNT(1*MID(A1,ROW($1:$500),1))))=7,1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$500),1)),0),COUNT(1*MID(A1,ROW($1:$500),1))),"")

 

[snowball]

Hi Joel,

Thanks for the reply. This formula works if my string only includes a 7
digit number e.g. abc1234567def will return 1234567 but if my string is
abc1234567def1 I get an error.

Is there a way of extracting a 7 digit number while omitting any other
numbers that may appear in the alphanumeric string?

All the rows in my data will contain this 7 digit number - and I need to
extract this number for all rows - however some rows will include other
numbers which I don't want.

Thanks,
snowball

 

[Mike H]

Hi,

Try this array formula

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

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

 

[Ragdyer]

How about posting several *exact* examples of your data.

If we can see a pattern, or even the max number of digits *prior* to the 7
digit number, things might be accomplished much easier.

 

[snowball]

Thanks Mike,

This formula extracts the first group of digits in the string regardless of
the number of digits i.e. it doesn't search for the 7 digit number within the
string.

e.g. a123bcd765421ef will extract 123 when I would like it to extract
7654321. Is there any way to extract a 7 digit number and ignore any other
set of numbers (that are not 7 digits in a row)?

Thanks.

 

[snowball]

Hi,

I'll try to explain a bit more clearly the data I'm looking at:

The data consists of customer numbers, names and addresses but these 3
datasets can appear in any order with each cell. The customer number is
always 7 digits but there can also be other numbers for house numbers/post
codes etc. I'm trying to extract the 7 digit customer number but I'm having
problems because of the other numbers.

A few examples would be:
John 1234567 house5 XC17
1234567house5xc17
house5 xc17 1234567

In all these cases I want to extract the customer number 1234567. There will
rarely be any cases where there will be a group of seven digits in a row
other than the customer number so I was hoping there might be a formula that
could look at the string and find 7 digits in a row and extract this while
ignoring any other numbers which are generally just 1 or 2 digits together
i.e. the 5 and 17 in the examples above.

Thanks again.

 

[Ron Rosenfeld]

Hi,

I'm trying to extract a 7 digit number from an alphanumeric string (in Excel
2003)when the 7 digit number can appear anywhere in the string and the string
can also contain other numbers which I do not want. I am currently using the
formula
=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$500),1)),0),COUNT(1*MID(A1,ROW($1:$500),1)))
which works fine when the alphanumeric string only contains the 7 digit
number I want and no other numbers. However I get errors when the string
includes other numbers.

Is there a formula I could use which would extract the 7 digit number but
which would omit all other groups of, for example, 2 digits or 10 digits?

e.g. in the string "text 1 abc1234567trial10nfy" I would like to extract
1234567.

Many thanks for any help you can give.
snowball

It is possible, although complex, to do this with a formula. It is simple to
do it with a VBA User Defined Function.

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

=SevenDigits(A1)

in some cell.

==================================
Option Explicit
Function SevenDigits(s As String) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "(^|\D)(\d{7})(\D|$)"
If re.test(s) = True Then
Set mc = re.Execute(s)
SevenDigits = mc(0).submatches(1)
End If
End Function
==========================
--ron

 

[Ragdyer]

Give some more examples.

This works for what you posted, but I'm sure you probably have other
configurations.

=IF(ISERR(--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),7)),
RIGHT(A1,7),MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),7))

 

[barry houdini]

Hello snowball,

this should extract the last 7 digit number in your string....

=LOOKUP(10^7,MID(SUBSTITUTE(A1,"
",""),ROW(INDIRECT("1:"&LEN(A1))),7)+0)

 

[snowball]

Hi Ron, Thanks for this - it's worked really well and is exactly what I need.

Thanks to everyone else too who gave me Excel formulae - these worked for
most of my data but there were always a few exceptions which the Visual Basic
can capture.

 

[Ron Rosenfeld]

Hi Ron, Thanks for this - it's worked really well and is exactly what I need.

Thanks to everyone else too who gave me Excel formulae - these worked for
most of my data but there were always a few exceptions which the Visual Basic
can capture.

You're welcome. And thanks for the feedback.
--ron