Removing excess spaces & characters (postcodes)
VBA Telemetry pings you when your VBA projects fail
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Removing excess spaces & characters (postcodes)

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    London, England
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hi chaps, another easy one I'm sure. I have a column of approx 3000 postcodes to import into a database, however they all have either excess spaces, commas or other punctuation after the postcode which will not allow them to import correctly. How do I "cleanse" them into the format AB12 3CD?

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,775
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-02 00:10, Marcus wrote:
    Hi chaps, another easy one I'm sure. I have a column of approx 3000 postcodes to import into a database, however they all have either excess spaces, commas or other punctuation after the postcode which will not allow them to import correctly. How do I "cleanse" them into the format AB12 3CD?
    Care to give some typical examples that have extraneous punctuation like comma, period, etc.?

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    London, England
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    eg.

    CX1 1BY. .,,!
    ECRM 7DH. : .-

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The easiest quickest way is to download asap utilities FREE from:

    http://www.asap-utilities.com

    I get this sort of thing ALL the time. Use the Text>Remove all special characters.
    "Have a good time......all the time"
    Ian Mac

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,775
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-02 00:29, Marcus wrote:
    eg.

    CX1 1BY. .,,!
    ECRM 7DH. : .-
    ASAP-Utilities is good to have, as Ian suggested. However, it fails to remove "!" from your sample.

    In B1 enter:

    =SUBSTITUTE(TRIM(A1)," ","@",1)

    In C1 enter:

    =SUBSTITUTE(SUBSTITUTE(B1,RIGHT(B1,SUMPRODUCT((LEN(B1)-LEN(SUBSTITUTE(B1,{".",",",":","-","!"," "},""))))),""),"@"," ")

    Select B1:C1, double click on the fill handle in the lower right corner (the little black square) in order to copy down these formulas.

    Copy everything in C and execute Edit|Paste Special >Values. Hereafter you can delete columns A and B.

    If there are more puntuation marks I didn't cover, include them in the

    {".",",",":","-","!"," "}

    part of the second formula.

    Aladin

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Marcus

    It's extremely easy to create your own macro to do this. Try this one on a selected Column of data.


    Code:
    Sub GoAway()
    Dim i As Integer, strCha As String
    
     On Error Resume Next
        For i = 1 To 27
          strCha = Choose(i, "~", "`", "!", "@", "#", "$", "%", "^", _
            "&", "~*", "(", ")", "_", "[", "]", "{", "}", "|", "", ":", _
            ";", ",", "<", ".", ">", "?", "/")
        
            Selection.Replace What:=strCha, Replacement:="", LookAt:=xlPart, _
                      SearchOrder:=xlByRows, MatchCase:=False
       Next i
     On Error GoTo 0
     
    End Sub

  7. #7
    New Member
    Join Date
    Mar 2002
    Location
    London, England
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Cheers all for problem solving, and for giving me a few more ideas. Many thanks indeed.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com