Delete Repetitive occurrence in a cell

[Blessy Clara]

Hi,

i have an excel sheet with an Address column - The Zip code is repeated twice in the column and i wanna remove the repetition -

[TABLE="width: 500"]
<tbody>[TR]
[TD]Zurich House, Level 5, 201 Leichhardt St Spring Hill QLD 4004 , 4004[/TD]
[TD]Zurich House, Level 5, 201 Leichhardt St Spring Hill QLD 4004[/TD]
[/TR]
[TR]
[TD]Zurich House, 6/ 75 Denham St Townsville QLD 4810 , 4810[/TD]
[TD]Zurich House, 6/ 75 Denham St Townsville QLD 4810[/TD]
[/TR]
[TR]
[TD]Zunker St Burnett Heads QLD 4670 , 4670[/TD]
[TD]Zunker St Burnett Heads QLD 4670[/TD]
[/TR]
[TR]
[TD]Zaknic Pl Bunbury WA 6230[/TD]
[TD]Zaknic Pl Bunbury WA 6230[/TD]
[/TR]
[TR]
[TD]1st Floor, 7 Bayfield St Rosny TAS, 7018 Australia , 7018[/TD]
[TD]1st Floor, 7 Bayfield St Rosny TAS, 7018 Australia[/TD]
[/TR]
[TR]
[TD]address[/TD]
[TD]Result[/TD]
[/TR]
</tbody>[/TABLE]



Thank you in advance for your time and effort

 

[arthurbr]

Something like =TRIM(left(A1,len(a1)-6)) ?

 

[Peter_SSs]

Something like =TRIM(left(A1,len(a1)-6)) ?

If the sample data is accurate, then this would fail for the second last example in post 1.



@Blessy Clara
You could try this user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Note from the last 2 examples in my data, that I have tried to ensure that any repeated 4-digit code is in fact a postcode, not a street number, before it is deleted. See how it goes.

Function DedupePostcode(s As String) As String
  Static RX As Object

  If RX Is Nothing Then Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "([A-Z]{2,3}.*?)(\d{4})(.*?)( *,* *\2$)"
  DedupePostcode = RX.Replace(s, "$1$2$3")
End Function

Excel Workbook
	A	B
1	Zurich House, Level 5, 201 Leichhardt St Spring Hill QLD 4004 , 4004	Zurich House, Level 5, 201 Leichhardt St Spring Hill QLD 4004
2	Zurich House, 6/ 75 Denham St Townsville QLD 4810 , 4810	Zurich House, 6/ 75 Denham St Townsville QLD 4810
3	Zunker St Burnett Heads QLD 4670 , 4670	Zunker St Burnett Heads QLD 4670
4	Zaknic Pl Bunbury WA 6230	Zaknic Pl Bunbury WA 6230
5	1st Floor, 7 Bayfield St Rosny TAS, 7018 Australia , 7018	1st Floor, 7 Bayfield St Rosny TAS, 7018 Australia
6	2000 George St Sydney NSW 2000	2000 George St Sydney NSW 2000
7	1st Floor, 2000 George St Sydney NSW 2000, 2000	1st Floor, 2000 George St Sydney NSW 2000
Sheet1

 

[Biz]

Very nice UDF with Regscript.

 

[Blessy Clara]

Thank you to everyone for taking time to stop by and respond -
arthurbr - Thanks, but there was a problem, as the last stated Example which didnt hve Postal digit of 4 numbers - it removed the terms QLD Aus = Q kinda like that (jst an ex)
Peter_SSs
Thank you very much for the wonderful UDF - and for stunning perception of my requirement (considering records without zipcode) - this is exactly what i required

 

[Peter_SSs]

Peter_SSs
Thank you very much for the wonderful UDF - and for stunning perception of my requirement (considering records without zipcode) - this is exactly what i required

Glad to help.