Splitting cell data

Hi,

No formula necessary. The easiest way is the Text To Columns functionality using the space character as a delimiter.

You'll end up with 4 columns with an X in the 3rd column so just select that column and delete.

Would you be willing to use the Text to columns command? https://support.office.com/en-us/art...A-7A3E9C363ED7 It looks like it should be a simple delimited text to columns with space as the delimiter. If desired, you can have it skip over the 1st column (with "TUBE" in it) and the 4th column (with "X") in it, so that it only outputs those three columns.

Also have do you carry out the command on multiples rows.

Simply select those rows before executing the command (in your sample file, select A2:A7 before calling up the Text to Columns command)

however I need data to also stay in the original cell

In the help file I linked to, step 7 through 9 (the "step 3" dialog of the text import wizard) desribe using the "destination" field to specify where to output the data. By default, this field will contain the upper left cell of the selected range so that the destination will be the same as the source range. If you want to keep the original data in column A, then simply specify a range that does not include column A for the destination range.

Here is a formula solution
Enter in F2 and copy across to H2 and down

Formula:

Please Login or Register  to view this content.

	A	B	C	D	E	F	G	H
1	Description 1	OD	ID	Length		OD	ID	Length
2	TUBE OD100 ID65 X L150	OD100	ID65	L150		OD100	ID65	L150
3	TUBE OD101.5 ID66 X L150	OD101.5	ID66	L150		OD101.5	ID66	L150
4	TUBE OD101.5 ID78 X L190					OD101.5	ID78	L190
5	TUBE OD103 ID76 X L190					OD103	ID76	L190
6	TUBE OD105 ID77.5 X L165					OD105	ID77.5	L165
7	TUBE OD106.5 ID77.35 X L165					OD106.5	ID77.35	L165

Sheet: Sheet1

Hi,

There's no way short of a macro that will replace the original single column of cells with three columns of result cells - either with functions or TextToColumns without some intermediated steps.

But how difficult is it to use TextToColumns or formulae and then delete the original column?

To perform TextToColumns on multiple rows simply select multiple cells before performing the TextToColumns