CUSTOME SPLIT TEXT various lengths

[mval]

Hi experts in Excel!

I have a problem that I can't solve, and after hours of research I could not find anything that would be help me, so here I am asking for help.

Here is my problem:

I have a spreed sheet with about 10,000 rows in one column A (RAW DATA) the data is in different lengths and I want to split each field to 3 B (DESC1), C (DESC2) and D (DESC3). Here is an example:

RAW DATA	DESC1	DESC2	DESC3
CENTRIFUGE FEED PUMP 2 & 3 COMMON ISOLATION VALVE 2 CLOSED	CENTRIFUGE FEED	PUMP 2 & 3 COMMON	ISOLATION VALVE 2 CLOSED
CENTRIFUGE FEED PUMP 2 & 3 COMMON ISOLATION VALVE 2 OPENED	CENTRIFUGE FEED	PUMP 2 & 3 COMMON	ISOLATION VALVE 2 OPENED
CENTRIFUGE FEED PUMP 3 & 4 COMMON ISOLATION VALVE CLOSED	CENTRIFUGE FEED	PUMP 3 & 4 COMMON	ISOLATION VALVE 2 CLOSED
CENTRIFUGE FEED PUMP 3 & 4 COMMON ISOLATION VALVE OPENED	CENTRIFUGE FEED	PUMP 3 & 4 COMMON	ISOLATION VALVE 2 OPENED
THICKENING CENTRIFUGE 1 LEVEL HIGH HIGH	THICKENING	CENTRIFUGE 1	LEVEL HIGH HIGH
THICKENING CENTRIFUGE 1 LEVEL LOW LOW	THICKENING	CENTRIFUGE 1	LEVEL LOW LOW
THICKENING CENTRIFUGE 1 THICKENED SLUDGE DENSITY FAULT	THICKENING CENTRIFUGE 1	THICKENED SLUDGE	DENSITY FAULT
THICKENED SLUDGE SAMPLE PUMP 1 DISCHARGE PRESSURE HIGH/LOW	THICKENED SLUDGE SAMPLE	SAMPLE PUMP 1	DISCHARGE PRESSURE HIGH/LOW
THICKENED SLUDGE SAMPLE PUMP 1 FAIL	THICKENED SLUDGE SAMPLE	SAMPLE PUMP 1	FAIL
THICKENED SLUDGE SAMPLE PUMP 1 IN REMOTE	THICKENED SLUDGE SAMPLE	SAMPLE PUMP 1	IN REMOTE
THICKENED SLUDGE SAMPLE PUMP 1 RUNNING	THICKENED SLUDGE SAMPLE	SAMPLE PUMP 1	RUNNING
THICKENED SLUDGE SAMPLE PUMP 1 SEAL WATER PRESSURE LOW	THICKENED SLUDGE SAMPLE	SAMPLE PUMP 1	SEAL WATER PRESSURE LOW
THICKENED SLUDGE SAMPLE PUMP 1 STATOR TEMP HIGH	THICKENED SLUDGE SAMPLE	SAMPLE PUMP 1	STATOR TEMP HIGH

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

the lengths of the DATA RAW varies throughout.

can someone help me?
thank you in advance and I hope provide enough information.

thank you!!

 

[BobUmlas]

Unfortunately, without a consistent pattern, this simply cannot be done with a macro.
Example:
THICKENING CENTRIFUGE 1 LEVEL LOW LOW
splits very differently from
THICKENING CENTRIFUGE 1 THICKENED SLUDGE DENSITY FAULT
In the first case the CENTRIFUGE 1 is Desc 2 and in the other it's part of Desc 1.
There's no way to know where the slit occurs.
Even using the new feature in 2013, flash fill, it would be full of "mistakes" because there's no recognizable pattern.
10,000 rows? You should be able to do that manually in under 2 weeks, I'd say. Well, at 8 hours a day, anyway!
Sorry guy.

 

[mval]

BobUmlas,

Thank you for your input!!

I guess, I need to get busy!! LOL

Thank you again for your reply!!

 

[Rick Rothstein]

Unfortunately, without a consistent pattern, this simply cannot be done with a macro.
Example:
THICKENING CENTRIFUGE 1 LEVEL LOW LOW
splits very differently from
THICKENING CENTRIFUGE 1 THICKENED SLUDGE DENSITY FAULT
In the first case the CENTRIFUGE 1 is Desc 2 and in the other it's part of Desc 1.
There's no way to know where the split occurs.

Just to follow up (in case you have access to the process that generated you raw data... if you could introduce a delimiter between the parts you want to split out (say a comma or comma/space for example), then a relatively simple formula is available, but given you data uses spaces for its delimiter and that data has internal spaces through out, there is no way to distinguish which spaces are delimiters and which are part of the data.

 

[mval]

Rick,

Thanks for the reply!!

I don't have the source of the data, so what I started to do is to split the column to several columns and CONCATENATE the info as I need it.

Thank you again!!