Removing excess spaces & characters (postcodes)

[Marcus]

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?

 

[Aladin Akyurek]

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.?

 

[Marcus]

eg.

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

 

[Ian Mac]

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.

 

[Aladin Akyurek]

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

 

[Dave Hawley]

Hi Marcus

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

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

 

[Marcus]

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