Microsoft Excel 2003 (full product)

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 ?

Hi, 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 FinishThere 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:

B1

=LEFT(A1,FIND("x",A1,1)-1)

C1

=MID(A1,FIND("x",A1,1)+1,FIND("x",A1,FIND("x",A1,1)+1)-FIND("x",A1,1)-1)

D1

=RIGHT(A1,LEN(A1)-(LEN(B1)+LEN(C1)+2))Regards

first option with Text to Columns have tried already but not working also tried now with your formlas but do not get any result

???

it works now when I changed "," from formula into ";" thanks a lot for your help

much appreciatedbrgds

Here's a method I'll bet you didn't consider... Paste 100x200x300 in all three cells.

- Click in A1.

- In the formula bar highlightx200x300

- 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.

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.

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

now also this works fine...small detail is making it work thks again

Ask Your Question

Weekly Poll

Did you buy anything on Amazon's Prime Day?

Discuss in The Lounge

Poll History