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

No.	EXAMPLE OF DATA
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} <tbody> </tbody>
2	XXXXXXX XXXXXXX: 54321_GGGGGGG {SITE NO 54321 TOWN HOUSE, SITE NO 54321 FIRE HOME ALARM, SITE NO 54321 ORANGE JUS} <tbody> </tbody>
3	XXXXXXX XXXXXXX: 12345_XXXXXXXXXXXXXX {SITE NO. 12345_CANDLE LIGHT} <tbody> </tbody>
4	XXXXXXX XXXXXXX: 12345_XXXXXX {SITE NO. 12345 - APPLE ORANGE, SITE NO. 12345 - BAG SHOES RED, SITE NO. 12345 - ID CARD NUMBER} <tbody> </tbody>

<tbody>
</tbody>

<tbody>
</tbody>

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

FORMULA =TRIM(MID($C$6,FIND("-",$C$6)+1,FIND("}",$C$6,FIND("-",$C$6))-FIND("-",$C$6)-1)) 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 <tbody> </tbody>

<tbody>
</tbody>

 

[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
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

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet624​

Worksheet Formulas Cell 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),"")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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.