Extracting 7 digit number from alphanumeric string

  • Thread starter Thread starter snowball
  • Start date Start date
S

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
 
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))),"")
 
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
 
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
 
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.
 
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.
 
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.
 
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
Click to expand...

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
 
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))
 
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.
 
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.
Click to expand...

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