Extracting specific text from string

[richardthelionheart]

=TRIM(LEFT(SUBSTITUTE(MID(D189,FIND("1",D189),LEN(D189))," ",REPT(" ",5)),5)) I'm using this formula to extract text from a string that begins with 1. {after the word find we see 1} This works fine except I now want to insert 1 or 2 where we see 1. So any text that begins with 1 or 2. I'm not sure this is possible ?? I would appreciate any help.

<colgroup><col width="64"></colgroup><tbody>
</tbody>

 

[admiral100]

Hi,

Try this ,not tested...

Ctrl+Shift+Enter NOT just Enter :

=TRIM(LEFT(SUBSTITUTE(MID(D18,MIN(IFERROR(FIND({"1","2"},D18),"")),LEN(D18))," ",REPT(" ",5)),5))

 

[richardthelionheart]

I'm confused as to exact procedure. Normally I copy the formula and paste into the workbook. I'm sorry but I would need detailed instructions as to how you mean, admiral

 

[FormR]

Hi, here is another option you can try:

=TRIM(LEFT(SUBSTITUTE(MID(D189,FIND(" 1"," "&SUBSTITUTE(D189,"2","1")),LEN(D189))," ",REPT(" ",5)),5))

 

[richardthelionheart]

Many thanks to the contributors. FormR - you solved it !!!!!!!!!! Great !!!!!!!!!! I'm really happy.