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

[Dustin R]

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.

 

[eduzs]

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),"")

 

[JoeMo]

Welcome to the forum! Copy B1 down.

Excel Workbook
	A	B
1	eLearning AT3050 9:58am EST (New York......	AT3050
2	OBSERVATION O200050 10:32am EST	*
Sheet2

 

[Dustin R]

Welcome to the forum! Copy B1 down.
Sheet2

*	A	B
1	eLearning AT3050 9:58am EST (New York......	AT3050
2	OBSERVATION O200050 10:32am EST	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:284px;"><col style="width:51px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula 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?

 

[Dustin R]

Thank you for your help. I tried the formula and it comes back with a value error.

 

[JoeMo]

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
	A	B
1	eLearning AT3050 9:58am EST (New York......	AT3050
2	OBSERVATION O200050 10:32am EST	O200050
Sheet1

 

[Dustin R]

Sorry, I just read your initial post again and see I left out the second key word. Try this instead:

Sheet1

*	A	B
1	eLearning AT3050 9:58am EST (New York......	AT3050
2	OBSERVATION O200050 10:32am EST	O200050

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:284px;"><col style="width:59px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula 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.

 

[eduzs]

IS there a way to adapt this formula to get the 2nd, 3th... word in the phrase? Thanks

 

[JoeMo]

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.