Parse Data which has no delimiter and no fixed width.

[Sotark]

Currently I receive a file that shows different jobs functions and the files related to the jobs, however the list of files received is shoved together in one cell as one long string. A few of the file names have a carraige return between them and some have a space but there is no fixed delimiter and the file names can be different lengths. The only constant is the file naming format. Each file starts with LZ.(the third character is a .) or LC. Some of these have two or three file names and some have 15-20. I am trying to find a way to see if I can get each file name in it s own column.

How data looks now:

Job <tbody> </tbody>	Frequency <tbody> </tbody>	Files <tbody> </tbody>
LZCD00001 <tbody> </tbody>	Daily <tbody> </tbody>	LC.AHJ123.AS345.CROC LC.lkj123.open123.G* LC.JDH123KLJ45.G* <tbody> </tbody>

<tbody>
</tbody>

How I would like it to look:

Job <tbody> </tbody>	Frequency <tbody> </tbody>
LZCD00001 <tbody> </tbody>	Daily <tbody> </tbody>	LC.AHJ123.AS345.CROC <tbody> </tbody>	LC.lkj123.open123.G* <tbody> </tbody>	LC.JDH123KLJ45.G* <tbody> </tbody>

<tbody>
</tbody>

I have tried using Text to Columns, substr and LEFT/RIGHT with SEARCH and can not seem to find a proper solution.

Any help would be greatly appreciated.

 

[DanteAmor]

Try this to For names that start with LC.

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:55.13px;" /><col style="width:150.18px;" /><col style="width:153.98px;" /><col style="width:154.93px;" /><col style="width:128.32px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:56px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >LZCD00001</td><td >Daily</td><td >LC.AHJ123.AS345.CROC LC.lkj123.open123.G*<br />LC.JDH123KLJ45.G*</td><td >LC.AHJ123.AS345.CROC</td><td >LC.lkj123.open123.G*<br /></td><td >LC.JDH123KLJ45.G*</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=TRIM(MID(SUBSTITUTE($C2,"LC.",REPT(" ",99)&"LC."),COLUMNS($D$1:D1)*100,COLUMNS($D$1:D1)+100))</td></tr></table></td></tr></table>

 

[DanteAmor]

Edit:

=TRIM(MID(SUBSTITUTE($C2,"LC.",REPT(" ",99)&"LC."),COLUMNS($D$1:D1)*100,100))

Drag to the right