Breaking up address with commas

[trumpetman]

I am trying to print some address labels.

I have an excel sheet and the address is all in one cell column Column D. What I am looking to do if possible, return in Column E the house Number. Then in F the street name. Column G the Town and finally the Post code in Columh H.

Are is there another way to break the address and when it sees a comma to then return the next line underneath?

[Special-K]

You need to supply some test data

[Arkadi]

trumpetman can you supply a sample workbook?

[trumpetman]

I have attach a quick one line example. I want to break the address up either so that the number comes back in e, then street in column f etc. Or if there is another way I am open to that as well.

Thanks

[Arkadi]

I have tried to put in a macro that may do what you want, button included. Let me know if this works for you.

[AlKey]

use this formula in E2 and pull it to the right until you see blanks

=TRIM(MID(SUBSTITUTE(","&$D$2,",",REPT(" ",50)),50*COLUMNS($A:A),50))

Row\Col	A	B	C	D	E	F	G	H
2	Mr	Ian	Test	1, Letsby Ave, Jail, XX1 1YZ	1	Letsby Ave	Jail	XX1 1YZ

[Arkadi]

Nice AlKey, can't say I follow the formula quite to the end, but good solution. (no idea what REPT is)

[AlKey]

Nice AlKey, can't say I follow the formula quite to the end, but good solution. (no idea what REPT is)

The REPT function withing SUBSTITUTE is used to replace a character with a bunch of spaces.

[trumpetman]

Thanks guys that's great, thanks.