# CONCATENATE TEXT from the middle of Words & Characters

#### coolzsters

##### New Member
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>

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Hi,

This works for your given samples, formula copied down:

Book1
AB
1XXXXXXX 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
2XXXXXXX 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
3XXXXXXX XXXXXXX: 12345_XXXXXXXXXXXXXX {SITE NO. 12345_CANDLE LIGHT}CANDLE LIGHT
4XXXXXXX 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
RangeFormula
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),""))

Hi,

This works for your given samples, formula copied down:

AB
1XXXXXXX 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
2XXXXXXX 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
3XXXXXXX XXXXXXX: 12345_XXXXXXXXXXXXXX {SITE NO. 12345_CANDLE LIGHT}CANDLE LIGHT
4XXXXXXX 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
CellFormula
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 ??

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

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.

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"

=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...

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.

Threads
1,203,219
Messages
6,054,216
Members
444,711
Latest member
Stupid Idiot

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back