Not able to extract correct data with the Mid formula.

[Jagdev Singh]

Hi Experts

I need to pull word from the middle of the text and I reached somewhere close to it.

Data

Injury: Laceration To: Finger(s)
Injury: Strain To: Lower Back Area
Injury: Contusion To: Multiple Head Injury

Out put

Laceration
Strain
Contusion

Formula I am trying is - "=MID(G2,9,FIND(" ",G2))"

It is not pulling the entire word, but only few letters from the word. Please let me know what I am doing wrong here.

Regards,
JD

 

[Deepak]

You are almost there!!

=MID(G2,9,FIND(" ",G2,9)-9)

 

[Deepak]

You are almost there!!

=MID(G2,9,FIND(" ",G2,9)-9)

There would be some alternatives too!!!
=MID(G2,9,FIND(" To:",G2)-9)

 

[Deepak]

There would be some alternatives too!!!
=MID(G2,9,FIND(" To:",G2)-9)

Another..

=TRIM(MID(SUBSTITUTE(G2," ",REPT(" ",99)),99,99))
=TRIM(MID(SUBSTITUTE(G2,"To: ",REPT(" ",99)),9,99))

 

[Jagdev Singh]

Hi Deepak

Just fantastic formulas!

Regards,
Jagga Dakku!

 

[finlain]

Another..

=TRIM(MID(SUBSTITUTE(G2," ",REPT(" ",99)),99,99))
=TRIM(MID(SUBSTITUTE(G2,"To: ",REPT(" ",99)),9,99))

Love this!Thank you very much.
Elaine

 

[Deepak]

Love this!Thank you very much.
Elaine

@ finlain
Thnx Buddy!!