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>

;;;;;;VIC;

;;;;;;VIC;

;;;;SA;;;

;;;;;;VIC;

;;;;;;VIC;

;;;;;;VIC;

;NSW;NT;QLD;SA;TAS;VIC;WA

;;;;;;VIC;

;;;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 NSW;NT;QLD;SA;TAS;VIC;WA VIC QLD NSW;VIC NSW;TAS:VIC <tbody> </tbody> Thanks in advance, Kevin

<tbody>
</tbody>

 

[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!!