Solved i need to split one cell into 5

February 21, 2013 at 09:42:10
Specs: Windows XP

i need this to go into 5 cells
16-16-040-08W4
so 16 in one cell
16 in another cell
040 into another cell
08 into another cell and
W4 into another cell.

See More: i need to split one cell into 5

Report •


#1
February 21, 2013 at 13:07:10

You can use the Text To Columns for most, then simply split the last one by hand.

Select your cell with the data.
On the Data menu, click Text to Columns.
When the Convert Text To Columns Wizard comes up,
make sure that the Delimited option is selected,
click Next.
Select the Other check box under Delimiters,
in the input box enter a dash
you should be able to see how your data will be split in the window
and then click Finished

That leaves only the 08W4 to split.

MIKE

http://www.skeptic.com/


Report •

#2
February 21, 2013 at 14:57:08
✔ Best Answer

Unfortunately, Text-To-Columns will strip off the leading zero from 040.

The Text-To-Column preview window will show 040, but the leading zero will disappear when the operation is complete.

Another way, which will retain the leading zero are the following formulas in B1:F1 -

B1:  =MID($A$1,1,2)
C1:  =MID($A$1,4,2)
D1:  =MID($A$1,7,3)
E1:  =MID($A$1,11,2)
F1:  =RIGHT($A$1,2)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions


Ask Question