Extracting specific text from string

[richardthelionheart]

<colgroup><col width="64"></colgroup><tbody>
[TD="class: xl63, width: 64"]=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.


[/TD]

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