Excel combining "IF" and "MID" in a formula to pull a specific word.

Dustin R

New Member
Joined
Apr 12, 2017
Messages
9
Hello everyone I am new to the page and I am in need of some Excel help. What I am trying to do is pull the second word out of a string of text if the first word matches. Here are the examples of the string of text I am working with.
eLearning AT3050 9:58am EST (New York......
OBSERVATION O200050 10:32am EST (New York......
What I need is Excel to look at the first word and if it matches "eLearning" to retrieve the AT3050 or if it matches "OBSERVATION" to pull the O200050. I tried using MID formula however, the first words are not the same length either are the second words. Now I am stuck. Can anyone help with this.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Try this:

=IF(or(LEFT(A1,5)="eLear",LEFT(A1,5)="OBSER"),MID(A1,SEARCH(" ",A1)+1,SEARCH(" ",A1,SEARCH(" ",A1)+1)-SEARCH(" ",A1)-1),"")
 
Last edited:
Upvote 0
Welcome to the forum! Copy B1 down.
Excel Workbook
AB
1eLearning AT3050 9:58am EST (New York......AT3050
2OBSERVATION O200050 10:32am EST*
Sheet2
 
Upvote 0
Welcome to the forum! Copy B1 down.
Sheet2

*AB
eLearning AT3050 9:58am EST (New York......AT3050
OBSERVATION O200050 10:32am EST*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:284px;"><col style="width:51px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
B1=IF(LEFT(TRIM(A1),9)="eLearning",TRIM(MID(SUBSTITUTE(TRIM(A1)," ",REPT(" ",100)),100,100)),"")

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

Thank you for taking the time to help me.
When I tried this it comes back blank am I doing something wrong?
 
Upvote 0
Thank you for taking the time to help me.
When I tried this it comes back blank am I doing something wrong?
Sorry, I just read your initial post again and see I left out the second key word. Try this instead:

Excel Workbook
AB
1eLearning AT3050 9:58am EST (New York......AT3050
2OBSERVATION O200050 10:32am ESTO200050
Sheet1
 
Last edited:
Upvote 0
Sorry, I just read your initial post again and see I left out the second key word. Try this instead:

Sheet1

*AB
eLearning AT3050 9:58am EST (New York......AT3050
OBSERVATION O200050 10:32am ESTO200050

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:284px;"><col style="width:59px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
B1=IF(OR(LEFT(TRIM(A1),9)="eLearning",LEFT(TRIM(A1),11)="OBSERVATION"),TRIM(MID(SUBSTITUTE(TRIM(A1)," ",REPT(" ",100)),100,100)),"")

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Thank you so much that worked perfectly.
 
Upvote 0
IS there a way to adapt this formula to get the 2nd, 3th... word in the phrase? Thanks
 
Upvote 0
IS there a way to adapt this formula to get the 2nd, 3th... word in the phrase? Thanks
Yes there is a way. Post some examples of the strings you are working with and what result you want for each.
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,314
Members
452,634
Latest member
cpostell

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

Which adblocker are you using?

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
Back
Top