CUSTOME SPLIT TEXT various lengths

mval

New Member
Joined
Jan 23, 2009
Messages
39
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
DESC1DESC2DESC3
CENTRIFUGE FEED PUMP 2 & 3 COMMON ISOLATION VALVE 2 CLOSEDCENTRIFUGE FEED PUMP 2 & 3 COMMONISOLATION VALVE 2 CLOSED
CENTRIFUGE FEED PUMP 2 & 3 COMMON ISOLATION VALVE 2 OPENEDCENTRIFUGE FEED PUMP 2 & 3 COMMONISOLATION VALVE 2 OPENED
CENTRIFUGE FEED PUMP 3 & 4 COMMON ISOLATION VALVE CLOSEDCENTRIFUGE FEED PUMP 3 & 4 COMMONISOLATION VALVE 2 CLOSED
CENTRIFUGE FEED PUMP 3 & 4 COMMON ISOLATION VALVE OPENED
CENTRIFUGE FEED PUMP 3 & 4 COMMONISOLATION VALVE 2 OPENED
THICKENING CENTRIFUGE 1 LEVEL HIGH HIGHTHICKENINGCENTRIFUGE 1LEVEL HIGH HIGH
THICKENING CENTRIFUGE 1 LEVEL LOW LOWTHICKENINGCENTRIFUGE 1
LEVEL LOW LOW
THICKENING CENTRIFUGE 1 THICKENED SLUDGE DENSITY FAULTTHICKENING CENTRIFUGE 1THICKENED SLUDGEDENSITY FAULT
THICKENED SLUDGE SAMPLE PUMP 1 DISCHARGE PRESSURE HIGH/LOWTHICKENED SLUDGE SAMPLESAMPLE PUMP 1DISCHARGE PRESSURE HIGH/LOW
THICKENED SLUDGE SAMPLE PUMP 1 FAILTHICKENED SLUDGE SAMPLESAMPLE PUMP 1FAIL
THICKENED SLUDGE SAMPLE PUMP 1 IN REMOTETHICKENED SLUDGE SAMPLESAMPLE PUMP 1
IN REMOTE
THICKENED SLUDGE SAMPLE PUMP 1 RUNNINGTHICKENED SLUDGE SAMPLESAMPLE PUMP 1RUNNING
THICKENED SLUDGE SAMPLE PUMP 1 SEAL WATER PRESSURE LOWTHICKENED SLUDGE SAMPLESAMPLE PUMP 1SEAL WATER PRESSURE LOW
THICKENED SLUDGE SAMPLE PUMP 1 STATOR TEMP HIGHTHICKENED SLUDGE SAMPLESAMPLE PUMP 1STATOR 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!!
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

BobUmlas

Well-known Member
Joined
Mar 14, 2002
Messages
1,170
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

New Member
Joined
Jan 23, 2009
Messages
39
BobUmlas,

Thank you for your input!!

I guess, I need to get busy!! LOL

Thank you again for your reply!!

:)
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,337
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Jan 23, 2009
Messages
39
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!!

:)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,522
Messages
5,529,330
Members
409,863
Latest member
stacy09
Top