Converting text to 10 columns with formula

Innoguide

Board Regular
Joined
Sep 13, 2011
Messages
159
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!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Hello Innoguide; try this.

B1, copy across.

=MID(SUBSTITUTE(TRIM($A1),";",REPT(" ",50)),COLUMNS($B1:B1)*50-49,50)
 
Upvote 0
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)
 
Upvote 0
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))
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top