Removing excess spaces & characters (postcodes)

Marcus · Apr 2nd, 2002, 01:10 AM

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 · Apr 2nd, 2002, 01:18 AM

Quote:

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 · Apr 2nd, 2002, 01:29 AM

eg.

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

Ian Mac · Apr 2nd, 2002, 01:59 AM

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 · Apr 2nd, 2002, 02:20 AM

Quote:

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 · Apr 2nd, 2002, 02:44 AM

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

Marcus · Apr 2nd, 2002, 02:53 AM

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

Apr 2nd, 2002, 01:59 AM	#4
Ian Mac MrExcel MVP Join Date: Feb 2002 Location: Newcastle, UK Posts: 1,174	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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 2nd, 2002, 01:10 AM	#1
Marcus New Member Join Date: Mar 2002 Location: London, England Posts: 37	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?

Apr 2nd, 2002, 01:29 AM	#3
Marcus New Member Join Date: Mar 2002 Location: London, England Posts: 37	eg. CX1 1BY. .,,! ECRM 7DH. : .-

Apr 2nd, 2002, 02:53 AM	#7
Marcus New Member Join Date: Mar 2002 Location: London, England Posts: 37	Cheers all for problem solving, and for giving me a few more ideas. Many thanks indeed.