CONCATENATE TEXT from the middle of Words & Characters

[coolzsters]

Hi all Master,
I'm really confused to grab TEXT from words, i already trying used many formula but still

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]No.[/TD]
[TD]EXAMPLE OF DATA[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 1187"]
<tbody>[TR]
[TD="class: xl67, width: 1187"]XXXXXXX XXXXXXX: 111111_CCCCCCC {SITE NO_111111_BLUE CAR DAYS, SITE NO_111111_RED HOT MOON, SITE NO_111111_THUNDER STORM, SITE NO_111111_GUN REPLACED}[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 1187"]
<tbody>[TR]
[TD="class: xl67, width: 1187"]XXXXXXX XXXXXXX: 54321_GGGGGGG {SITE NO 54321 TOWN HOUSE, SITE NO 54321 FIRE HOME ALARM, SITE NO 54321 ORANGE JUS}[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 1187"]
<tbody>[TR]
[TD="class: xl67, width: 1187"]XXXXXXX XXXXXXX: 12345_XXXXXXXXXXXXXX {SITE NO. 12345_CANDLE LIGHT}[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 1187"]
<tbody>[TR]
[TD="class: xl67, width: 1187"]XXXXXXX XXXXXXX: 12345_XXXXXX {SITE NO. 12345 - APPLE ORANGE, SITE NO. 12345 - BAG SHOES RED, SITE NO. 12345 - ID CARD NUMBER}[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1215"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

i used below formula for calculate in No.4 but "SITE NO. 12345" always include

[TABLE="width: 1187"]
<tbody>[TR]
[TD="class: xl64, width: 1187"]FORMULA =TRIM(MID($C$6,FIND("-",$C$6)+1,FIND("}",$C$6,FIND("-",$C$6))-FIND("-",$C$6)-1))

[TABLE="width: 1187"]
<tbody>[TR]
[TD="class: xl66, width: 1187"]RESULT = APPLE ORANGE, SITE NO. 12345 -BAG SHOES RED, SITE NO. 12345 -ID CARD NUMBER

MY EXPECTED = APPLE ORANGE, BAG SHOES RED, ID CARD NUMBER[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

This works for your given samples, formula copied down:

Book1
	A	B
1	XXXXXXX XXXXXXX: 111111_CCCCCCC {SITE NO_111111_BLUE CAR DAYS, SITE NO_111111_RED HOT MOON, SITE NO_111111_THUNDER STORM, SITE NO_111111_GUN REPLACED}	BLUE CAR DAYS, RED HOT MOON, THUNDER STORM, GUN REPLACED
2	XXXXXXX XXXXXXX: 54321_GGGGGGG {SITE NO 54321 TOWN HOUSE, SITE NO 54321 FIRE HOME ALARM, SITE NO 54321 ORANGE JUS}	TOWN HOUSE, FIRE HOME ALARM, ORANGE JUS
3	XXXXXXX XXXXXXX: 12345_XXXXXXXXXXXXXX {SITE NO. 12345_CANDLE LIGHT}	CANDLE LIGHT
4	XXXXXXX XXXXXXX: 12345_XXXXXX {SITE NO. 12345 - APPLE ORANGE, SITE NO. 12345 - BAG SHOES RED, SITE NO. 12345 - ID CARD NUMBER}	APPLE ORANGE, BAG SHOES RED, ID CARD NUMBER
Sheet624

Cell Formulas
Range	Formula
B1	=TRIM(REPLACE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"_"," "),".",""),"-",""),"}",""),"SITE NO "&TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",99)),MIN(FIND({0,1,2,3,4}+{0;5},A1&1/17)),99)),""),1,FIND("{",A1),""))

 

[coolzsters]

Hi,

This works for your given samples, formula copied down:

	A	B
XXXXXXX XXXXXXX: 111111_CCCCCCC {SITE NO_111111_BLUE CAR DAYS, SITE NO_111111_RED HOT MOON, SITE NO_111111_THUNDER STORM, SITE NO_111111_GUN REPLACED}	BLUE CAR DAYS, RED HOT MOON, THUNDER STORM, GUN REPLACED
XXXXXXX XXXXXXX: 54321_GGGGGGG {SITE NO 54321 TOWN HOUSE, SITE NO 54321 FIRE HOME ALARM, SITE NO 54321 ORANGE JUS}	TOWN HOUSE, FIRE HOME ALARM, ORANGE JUS
XXXXXXX XXXXXXX: 12345_XXXXXXXXXXXXXX {SITE NO. 12345_CANDLE LIGHT}	CANDLE LIGHT
XXXXXXX XXXXXXX: 12345_XXXXXX {SITE NO. 12345 - APPLE ORANGE, SITE NO. 12345 - BAG SHOES RED, SITE NO. 12345 - ID CARD NUMBER}	APPLE ORANGE, BAG SHOES RED, ID CARD NUMBER

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

</tbody>

Sheet624​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=TRIM(REPLACE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"_"," "),".",""),"-",""),"}",""),"SITE NO "&TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",99)),MIN(FIND({0,1,2,3,4}+{0;5},A1&1/17)),99)),""),1,FIND("{",A1),""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Wow Perfect..

but how if the SITE NO. was deference, i mean not only 1111/12345/54321...

Example :

B2389/234N894/345879G/CH72/PX161 ??

 

[coolzsters]

i forgot for example sometimes SITE NO without Space "SITE NOBX12398"

 

[jtakw]

You'll need to provide various samples of All possible text strings, and I'll see if I can come up with something, no guarantees.

 

[coolzsters]

You'll need to provide various samples of All possible text strings, and I'll see if I can come up with something, no guarantees.

ok noted jtakw, but maybe if with below example can be more complex...

Wow Perfect..

but how if the SITE NO. was deference, i mean not only 1111/12345/54321...

Example :

B2389/234N894/345879G/CH72/PX161 ??



forgot for example sometimes SITE NO without Space "SITE NOBX12398"

 

[coolzsters]

=TRIM(REPLACE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"_"," "),".",""),"-",""),"}",""),"SITE NO "&TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",99)),MIN(FIND({0,1,2,3,4}+{0;5},A1&1/17)),99)),""),1,FIND("{",A1),""))

Mr. jtakw..
It's Possible to add various below samples to Above Formula ?

ok noted jtakw, but maybe if with below example can be more complex...

 

[jtakw]

I need to see various samples of the Entire String, Not just bits and pieces of it.

Without such, it's Impossible to even try to come up with a formula.