Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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,R OW(1:99),1)),0),4),"") If this post helps click Yes --------------- Jacob Skaria "Sooz" wrote: 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. |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
On Tue, 18 Aug 2009 00:16:01 -0700, Sooz
wrote: 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 |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
On Tue, 18 Aug 2009 00:40:01 -0700, Jacob Skaria
wrote: 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 |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 "Jacob Skaria" wrote: 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,R OW(1:99),1)),0),4),"") If this post helps click Yes --------------- Jacob Skaria "Sooz" wrote: 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. |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
When you say 'Address fields' that can be of different types Street level
address, premise level address, sub premise level etc;... "Ron Rosenfeld" wrote: On Tue, 18 Aug 2009 00:40:01 -0700, Jacob Skaria wrote: 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 |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
On Tue, 18 Aug 2009 01:17:01 -0700, Jacob Skaria
wrote: 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 |
#8
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. "Sooz" wrote: 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. |
#9
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 the 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? "Lori Miller" wrote: 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. "Sooz" wrote: 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. |
#10
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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)) "Michael R" wrote: 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 the 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? "Lori Miller" wrote: 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. "Sooz" wrote: 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. |
#11
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Lori,
Thank you very much - this is impressive! Michael "Lori Miller" wrote: 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)) "Michael R" wrote: 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 the 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? "Lori Miller" wrote: 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. "Sooz" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Nested formula to search a text string and return specific text | Excel Worksheet Functions | |||
Search for a text string | Excel Discussion (Misc queries) | |||
search string for number value | Excel Discussion (Misc queries) | |||
How do a search for a text string using a formula | Excel Discussion (Misc queries) | |||
Search, find or lookup defined text in text string | Excel Worksheet Functions |