how to split content of one cell into more

Microsoft Excel 2003 (full product)
March 15, 2010 at 10:26:44
Specs: Windows XP
how to split content of one cell A1 100x200x300 in to three cells showing in first cell B1 100, in second C1 200 and in third D1 300 ?

See More: how to split content of one cell into more

March 15, 2010 at 10:50:27

If this is a one-off exercise, you can use 'Text to Columns'

Select cell A1, and from the Menu select Data - Text to Columns ...
Select Delimited
then Next, check the 'Other' check box and enter x in the text box as a delimiter.
Click Finish

There are also formulas that can go in cells B1, C1 & D1 to extract the numbers. This is of use if the contents of cell A1 will change, but the structure (number x number x number) will not change.

Here is one set of formulas that you could use:


Report •

March 15, 2010 at 11:23:41
first option with Text to Columns have tried already but not working

also tried now with your formlas but do not get any result


Report •

March 15, 2010 at 11:30:22
it works now when I changed "," from formula into ";"

thanks a lot for your help
much appreciated


Report •

Related Solutions

March 15, 2010 at 11:32:00
Here's a method I'll bet you didn't consider...

Paste 100x200x300 in all three cells.

- Click in A1.
- In the formula bar highlight x200x300
- Click the Font Color drop down and choose White.

Only the 100 will appear in A1.

Follow a similar procedure for B1 and C1 to display 200 and 300.

I know, it's probably not the best solution for this particular case, but it's something to keep in the back of your mind - the fact that you can format individual characters within a single cell.

It's a feature that I use very, very often.

Report •

March 15, 2010 at 11:37:51
To get exactly what you asked for via Text To Columns, you'd need to put 100x200x300 in B1 and then use Text To Columns, delimited by the x.

If you try it on A1, you'll lose the original string.

Report •

March 15, 2010 at 11:42:56
thanks for more info...

this I knew but would not be helpful as I needed to split many cells and calculate only first two amounts. the formula did great job...thks

Report •

March 15, 2010 at 11:45:36
now also this works fine...small detail is making it work

thks again

Report •

Ask Question