Removing semi-columns in front of, at the end of and between text

[kkuo155]

Hey guys,

I've been pulling my hair and trying to work this out, I was wondering if there's a formula that would allow me to remove the extra semi-columns, see data below:

<style type="text/css"><!--br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<tbody>[TR]
[TD];;;;;;VIC;[/TD]
[/TR]
[TR]
[TD];;;;;;VIC;[/TD]
[/TR]
[TR]
[TD];;;;SA;;;[/TD]
[/TR]
[TR]
[TD];;;;;;VIC;[/TD]
[/TR]
[TR]
[TD];;;;;;VIC;[/TD]
[/TR]
[TR]
[TD];;;;;;VIC;[/TD]
[/TR]
[TR]
[TD];NSW;NT;QLD;SA;TAS;VIC;WA[/TD]
[/TR]
[TR]
[TD];;;;;;VIC;[/TD]
[/TR]
[TR]
[TD];;;QLD;;;;
<style type="text/css"><!--br {mso-data-placement:same-cell;}--></style>;NSW;;;;;VIC;
<style type="text/css"><!--br {mso-data-placement:same-cell;}--></style>;NSW;;;;TAS;VIC;

How it should appear:
VIC
VIC
SA
VIC
VIC
VIC
[TABLE="width: 0"]
<tbody>[TR]
[TD]NSW;NT;QLD;SA;TAS;VIC;WA
VIC
QLD
NSW;VIC
NSW;TAS:VIC[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
Thanks in advance,
Kevin
[/TD]
[/TR]
</tbody>[/TABLE]

 

[sheetspread]

Excel 2010
	A	B
1	;;;;;;VIC;	VIC
2	;;;;;;VIC;	VIC
3	;;;;SA;;;	SA
4	;;;;;;VIC;	VIC
5	;;;;;;VIC;	VIC
6	;;;;;;VIC;	VIC
7	;NSW;NT;QLD;SA;TAS;VIC;WA	NSW;NT;QLD;SA;TAS;VIC;WA
8	;;;;;;VIC;	VIC
9	;;;QLD;;;;	QLD
10	;NSW;;;;;VIC;	NSW;VIC
11	;NSW;;;;TAS;VIC;	NSW;TAS;VIC
Sheet15

Cell Formulas
Range	Formula
B1	=SUBSTITUTE(TRIM(SUBSTITUTE(A1,";"," "))," ",";")

 

[kkuo155]

That's excellent, thank you so much sheetspread!!