Converting text to 10 columns with formula

[Innoguide]

I have a column of cells that each contain up to ten 1-3 digit unique identifiers separated by ; that need to be pulled out and placed in the ten adjacent columns in the order in which they first appear in the cell. There may be spaces which should be ignored (if that last requirement is a problem

So if the cell A1 contains 1;7;15; 23; 99;100 then:

B1=1
C1=7
D1=15
E1=23
F1=99
G1=100
H1=""
I1=""
J1=""
K1=""

Of course the easy way to do this would be with a macro using data- text to column, but this needs to be a macro free workbook if possible. So I need a formula for each cell and could add a cell if one is needed to strip out any spaces with substitute.

Thanks for any ideas!

 

[Gerald Higgins]

Maybe this thread from earlier today will help.
http://www.mrexcel.com/forum/showthread.php?t=587070

 

[Haseeb Avarakkan]

Hello Innoguide; try this.

B1, copy across.

=MID(SUBSTITUTE(TRIM($A1),";",REPT(" ",50)),COLUMNS($B1:B1)*50-49,50)

 

[Innoguide]

Brilliant Haseeb.
Thanks so much, this works perfectly. I can see how to change it from semi-colons to commas, but what would it take to allow either commas OR semi-colons?

Thanks again!

Hello Innoguide; try this.

B1, copy across.

=MID(SUBSTITUTE(TRIM($A1),";",REPT(" ",50)),COLUMNS($B1:B1)*50-49,50)

 

[Haseeb Avarakkan]

what would it take to allow either commas OR semi-colons?

One option is;

Use one more SUBSTITUTE function to convert all semi-colons to commas, or commas to semi-colons.

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(TRIM($A1),",",";"),";",REPT(" ",50)),COLUMNS($B1:B1)*50-49,50))

 

[Innoguide]

Thanks again Haseeb!

BTW - what is the significance of 50 in the formula?