search text string for number

[Sooz]

I want a formula that will search a cell and identify whether that cell
contains a postcode or not - and return that postcode. A postcode is
identified as being a 4 digit number between 0600 and 9990.

Ideally it won't matter where in the text the postcode appears. The cell
will be a text (address) field.

 

[Jacob Skaria]

If post code is the only numeric in the address try the below formula and
feedback. Please note that this is an array formula. Within the cell in edit
mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula>}"

With your address text in cell A1 try the below formula B1(array entered)

A1 = cross road,town,county, 2600

=IF(AND(COUNT(1*MID(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99),ROW(1:99),1))=4,--MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(1:99),1)),0),4)>=600,--MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(1:99),1)),0),4)<=9990),MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(1:99),1)),0),4),"")

If this post helps click Yes

 

[Ron Rosenfeld]

I want a formula that will search a cell and identify whether that cell
contains a postcode or not - and return that postcode. A postcode is
identified as being a 4 digit number between 0600 and 9990.

Ideally it won't matter where in the text the postcode appears. The cell
will be a text (address) field.

One way is with a 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

=PostCode(A1)

in some cell.


======================================
Option Explicit
Function PostCode(s As String) As String
Dim re As Object, mc As Object
Const sPat As String = "\b\d{4}\b"
Dim lPC As Long

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
If re.test(s) = False Then Exit Function
Set mc = re.Execute(s)
lPC = Val(mc(0))
If lPC >= 600 And lPC <= 9990 Then
PostCode = mc(0)
End If
End Function
===================================
--ron

 

[Ron Rosenfeld]

If post code is the only numeric in the address

Are there countries in which that would be a realistic assumption for address
fields?
--ron

 

[Jacob Skaria]

If you would like to use a UDF (User Defined function); try the below. From
workbook launch VBE using Alt+F11. From menu Insert a Module and paste the
below function.Close and get back to workbook and try the below formula.

=FINDPC(A1)


Function FindPC(strData As String) As String
Dim intTemp As Integer, varTemp As Variant
If strData Like "* #### *" Then
For intTemp = 1 To Len(strData)
If Mid(" " & strData & " ", intTemp, 6) Like " #### " Then
varTemp = Trim(Mid(" " & strData & " ", intTemp, 6))
If CInt(varTemp) >= 600 And CInt(varTemp) <= 9990 Then _
FindPC = CStr(varTemp): Exit Function
End If
Next
End If
End Function

If this post helps click Yes

 

[Jacob Skaria]

When you say 'Address fields' that can be of different types Street level
address, premise level address, sub premise level etc;...

 

[Ron Rosenfeld]

When you say 'Address fields' that can be of different types Street level
address, premise level address, sub premise level etc;...

At least in the US, all of those could contain numbers that might not be post
codes (zip codes here).
--ron

 

[Lori Miller]

For an address in A1, try entering this to return the postcode:

=MAX(MMULT({1,1,1},--TEXT(MID("."&A1&".",COLUMN(A:IV)
+{0;1;5},{1;4;1})&" 0/1","[>9990]-9999;[>=600]0;-9999;\0")),0)

If a postcode is not found in the address it returns 0.
If there were more than one postcode it returns the larger value.

 

[Michael R]

Lori,
This formula is a complex beast - but it fascinates me since it (almost)
works and that even without VBA.

In your fomula there are a number of items that I don't understand why they
are there:
1) the "." before and after A1
2) the 3 arrays {1,1,1}, {0,1,5}, {1,4,1}
3) the &" 0/1"

So I stripped them out leaving a simplified formula of
=MAX(MMULT(1,--TEXT(MID(A1,COLUMN(A:IV),4),"[>9990]-9999;[>=600]0;-9999;\0")),0)

.... and much to my surprise it works too; it works even better because in my
test string of
wuwu123456wuwu
.... containing 3 4 digit strings (1234, 2345, 3456)
your formula returns a 0, mine returns 3456.


Can you (or anybody else) enlighten me what the idea of the above mentioned
seemingly unnecessary features is?

For an address in A1, try entering this to return the postcode:

=MAX(MMULT({1,1,1},--TEXT(MID("."&A1&".",COLUMN(A:IV)
+{0;1;5},{1;4;1})&" 0/1","[>9990]-9999;[>=600]0;-9999;\0")),0)

If a postcode is not found in the address it returns 0.
If there were more than one postcode it returns the larger value.

I want a formula that will search a cell and identify whether that cell
contains a postcode or not - and return that postcode. A postcode is
identified as being a 4 digit number between 0600 and 9990.

Ideally it won't matter where in the text the postcode appears. The cell
will be a text (address) field.

 

[Lori Miller]

The original requirement stated:

"A postcode is identified as being a 4 digit number between 0600 and 9990"

The point of adding the extra parts to the formula was to make
it more robust**. My interpretation was that the formula should
only take such codes and exclude everything else, this means that
three digit numbers without a leading zero and parts of larger
numbers should be ignored. I would not agree that you should
treat such numbers as postcodes or return "999" from " 9991 ".

1&2) were added to restrict return values to these exact ranges
by checking that the surrounding characters are non-numeric.

Other numbers might occur in the house number for example.
Since the postcode is likely to be at the end of the address it
might be better to take the last match instead. You could do this
by replacing MAX([…],0) by LOOKUP(9999,1/1/[…]^0.5^2) in
the formula (this makes negative and zero values into errors.)

3) &" 0/1" was inserted to exclude any non-integer values from
the result as well as extra spaces by adding a zero fractional part.

If the address included text such as 12e2, 7e3, 1**3, these
should not be included. Other things to watch out for are items
that evaluate to dates or times which are in the numbers range
eg an appartment number 1/25 could evaluate to a 1900 date
depending on regional settings.

One more tweak to exclude possible negatives would be to
use --(0&TEXT(…)) instead of --TEXT(…)
____________
**If you want a simpler formula that does not account for 1&2 maybe try:
=LOOKUP(9999,FIND(ROW(600:9990),A1),ROW(600:9990))

Lori,
This formula is a complex beast - but it fascinates me since it (almost)
works and that even without VBA.

In your fomula there are a number of items that I don't understand why they
are there:
1) the "." before and after A1
2) the 3 arrays {1,1,1}, {0,1,5}, {1,4,1}
3) the &" 0/1"

So I stripped them out leaving a simplified formula of
=MAX(MMULT(1,--TEXT(MID(A1,COLUMN(A:IV),4),"[>9990]-9999;[>=600]0;-9999;\0")),0)

... and much to my surprise it works too; it works even better because in my
test string of
wuwu123456wuwu
... containing 3 4 digit strings (1234, 2345, 3456)
your formula returns a 0, mine returns 3456.


Can you (or anybody else) enlighten me what the idea of the above mentioned
seemingly unnecessary features is?

For an address in A1, try entering this to return the postcode:

=MAX(MMULT({1,1,1},--TEXT(MID("."&A1&".",COLUMN(A:IV)
+{0;1;5},{1;4;1})&" 0/1","[>9990]-9999;[>=600]0;-9999;\0")),0)

If a postcode is not found in the address it returns 0.
If there were more than one postcode it returns the larger value.

I want a formula that will search a cell and identify whether that cell
contains a postcode or not - and return that postcode. A postcode is
identified as being a 4 digit number between 0600 and 9990.

Ideally it won't matter where in the text the postcode appears. The cell
will be a text (address) field.

 

[Michael R]

Lori,

Thank you very much - this is impressive!

Michael

The original requirement stated:

"A postcode is identified as being a 4 digit number between 0600 and 9990"

The point of adding the extra parts to the formula was to make
it more robust**. My interpretation was that the formula should
only take such codes and exclude everything else, this means that
three digit numbers without a leading zero and parts of larger
numbers should be ignored. I would not agree that you should
treat such numbers as postcodes or return "999" from " 9991 ".

1&2) were added to restrict return values to these exact ranges
by checking that the surrounding characters are non-numeric.

Other numbers might occur in the house number for example.
Since the postcode is likely to be at the end of the address it
might be better to take the last match instead. You could do this
by replacing MAX([…],0) by LOOKUP(9999,1/1/[…]^0.5^2) in
the formula (this makes negative and zero values into errors.)

3) &" 0/1" was inserted to exclude any non-integer values from
the result as well as extra spaces by adding a zero fractional part.

If the address included text such as 12e2, 7e3, 1**3, these
should not be included. Other things to watch out for are items
that evaluate to dates or times which are in the numbers range
eg an appartment number 1/25 could evaluate to a 1900 date
depending on regional settings.

One more tweak to exclude possible negatives would be to
use --(0&TEXT(…)) instead of --TEXT(…)
____________
**If you want a simpler formula that does not account for 1&2 maybe try:
=LOOKUP(9999,FIND(ROW(600:9990),A1),ROW(600:9990))

Lori,
This formula is a complex beast - but it fascinates me since it (almost)
works and that even without VBA.

In your fomula there are a number of items that I don't understand why they
are there:
1) the "." before and after A1
2) the 3 arrays {1,1,1}, {0,1,5}, {1,4,1}
3) the &" 0/1"

So I stripped them out leaving a simplified formula of
=MAX(MMULT(1,--TEXT(MID(A1,COLUMN(A:IV),4),"[>9990]-9999;[>=600]0;-9999;\0")),0)

... and much to my surprise it works too; it works even better because in my
test string of
wuwu123456wuwu
... containing 3 4 digit strings (1234, 2345, 3456)
your formula returns a 0, mine returns 3456.


Can you (or anybody else) enlighten me what the idea of the above mentioned
seemingly unnecessary features is?

For an address in A1, try entering this to return the postcode:

=MAX(MMULT({1,1,1},--TEXT(MID("."&A1&".",COLUMN(A:IV)
+{0;1;5},{1;4;1})&" 0/1","[>9990]-9999;[>=600]0;-9999;\0")),0)

If a postcode is not found in the address it returns 0.
If there were more than one postcode it returns the larger value.


:

I want a formula that will search a cell and identify whether that cell
contains a postcode or not - and return that postcode. A postcode is
identified as being a 4 digit number between 0600 and 9990.

Ideally it won't matter where in the text the postcode appears. The cell
will be a text (address) field.