Announcement

Collapse
No announcement yet.

Extract postcode from text string

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Extract postcode from text string



    Hi,

    I am trying to extract postcodes from a text string, unfortunately the postcode doesn't always appear at the end of the text string.

    I'm not sure if this can be done with formulas or whether it requires VBA to accomplish the task.

    My aim is to extract the postcode which I would like to appear in column F.

    I have attached an example spreadsheet.

    Thanks in advance for any help.

    Ade
    Attached Files

  • #2
    Re: Extract postcode from text string

    Hi,

    There may be better ways..

    Try this UDF.

    Code:
    Function POSTCODE(ByVal InpStr As String) As String
        
        Dim w       As String
        Dim j       As Long
        Dim Ptrn1
        Dim Ptrn2   As String
        
        x = Split(Replace(InpStr, ",", " "), " ")
        
        Ptrn1 = Array("[A-Z][0-9]", "[A-Z][0-9][0-9]", "[A-Z][A-Z][0-9]", "[A-Z][A-Z][0-9][0-9]", _
                        "[A-Z][0-9][A-Z]", "[A-Z][A-Z][0-9][A-Z]")
        
        Ptrn2 = "[0-9]*" '"[0-9][A-Z][A-Z]"
        
        On Error Resume Next
        For i = 0 To UBound(x)
            w = x(i)
            For j = LBound(Ptrn1) To UBound(Ptrn1)
                If Len(w) Then
                    If w Like Ptrn1(j) And x(i + 1) Like Ptrn2 Then
                        If Err.Number <> 0 Then
                            Err.Clear
                            If w Like Ptrn1(j) & Ptrn2 Then
                                POSTCODE = w: Exit Function
                            End If
                        Else
                            POSTCODE = w & Space(1) & x(i + 1)
                            Exit Function
                        End If
                    ElseIf w Like Ptrn1(j) Then
                        POSTCODE = w: Exit Function
                    End If
                End If
            Next
        Next
    End Function
    Use like

    =POSTCODE(D2)

    HTH
    Kris

    ExcelFox

    Comment


    • #3
      Re: Extract postcode from text string

      Thanks for your help with this Krishnakumar.

      I've not tested it yet.

      Thanks again.

      Ade

      Comment


      • #4
        Re: Extract postcode from text string

        It works like a dream, thanks again Krishnakumar.

        Comment


        • #5
          Re: Extract postcode from text string

          Hi,

          You are welcome !!
          Kris

          ExcelFox

          Comment


          • #6
            Re: Extract postcode from text string

            Hi Krishnakumar

            This looks just what I need to extract postcodes from lines of text but I can't get it to work with Excel 2007. I think perhaps I need to convert the VB code to VBA but am not sure how. Any help would be much appreciated.

            Regards SRC

            Comment


            • #7
              Re: Extract postcode from text string

              Hello src

              This code is not specific to a certain version of Excel and should work in Excel 2007 as well.
              Can you tell us where you pasted the code for that custom function?
              And how you use the function in a worksheet?
              Regards,

              Wigi

              Excel MVP 2011-2014

              For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

              -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

              Comment


              • #8
                Re: Extract postcode from text string

                Originally posted by Wigi View Post
                Hello srcThis code is not specific to a certain version of Excel and should work in Excel 2007 as well.Can you tell us where you pasted the code for that custom function?And how you use the function in a worksheet?
                I'm afraid I'm a novice at all this and have spent several hours trying to puzzle it out. I downloaded the original worksheet postcode.xlsm from this thread as I assumed that the code was specific to it and I was planning to arrange my data in the same column format. I then recorded a new macro and pasted the code into it - see below. I added 'End Sub' at the end as I was getting error messages about it not being found. However trying to run the macro with the downloaded spreadsheet doesn't work and, stepping through it, it stops at 'As' in the first line.Thanks

                Comment


                • #9
                  Re: Extract postcode from text string

                  Hello there

                  You should not record a macro, but you need a module :-)
                  Just copy/paste the function code (post #2 above) into a normal module. Delete any other code you still have.
                  Use the function in the worksheet as indicated.
                  Regards,

                  Wigi

                  Excel MVP 2011-2014

                  For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

                  -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

                  Comment


                  • #10
                    Re: Extract postcode from text string

                    Originally posted by Wigi View Post
                    Hello thereYou should not record a macro, but you need a module :-)Just copy/paste the function code (post #2 above) into a normal module. Delete any other code you still have.Use the function in the worksheet as indicated.
                    Sorry I'm still struggling. I've opened the downloaded file postcode.xlsm. I've clicked on the Visual Basic icon under Developer. I've clicked on Insert new Module, I've pasted the code into this. I've saved postcode.xlsm. I've clicked on Close and Return to MS Excel. I've then clicked on the Macro icon but there are no macros shown. However having done this several times, when I click on Insert Module it wants to call it Module 6.

                    Comment


                    • #11
                      Re: Extract postcode from text string

                      You do not need to execute any macro.
                      Nor is it a macro you create (or copy/paste).

                      It is a function that you paste in a module in the file where you need it.
                      In the worksheet you then use (for cell D2):

                      =POSTCODE(D2)

                      as Krisnakumar already pointed out above.
                      Regards,

                      Wigi

                      Excel MVP 2011-2014

                      For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

                      -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

                      Comment


                      • #12
                        Re: Extract postcode from text string

                        Originally posted by Wigi View Post
                        You do not need to execute any macro.Nor is it a macro you create (or copy/paste).It is a function that you paste in a module in the file where you need it.In the worksheet you then use (for cell D2):=POSTCODE(D2)as Krisnakumar already pointed out above.
                        OK... thanks... I can see that I now have to find out how to use Functions and Modules. Apart from a Macro I managed to copy successfully from this site, I have only ever used simple formulae before. I said I was a novice! I will do some Googling.

                        Comment


                        • #13
                          Re: Extract postcode from text string

                          Then there's a lot to be explored and discovered still ;-)
                          Regards,

                          Wigi

                          Excel MVP 2011-2014

                          For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

                          -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

                          Comment


                          • #14
                            Re: Extract postcode from text string

                            Hi, I've been looking for something like this to use in Google Sheets, does anyone know how to convert it to use in Google Scripts?

                            I have however got it working in Excel 2013.

                            Now that this function successfully extracts the POSTCODES from the address cell and places it in its own cell how do I go about expanding the function to remove the POSTCODE text from that original cell?

                            Ian.
                            Last edited by design360; August 22nd, 2014, 20:11. Reason: Added an extra question

                            Comment


                            • #15


                              Re: Extract postcode from text string

                              Would it be possible to have the code commented so that it's easier to understand?

                              Comment

                              Working...
                              X