Extract 2nd to last word

[mozjesse]

How do I extract the second to last word from a string of text in one cell and put it another.

 

[Rick Rothstein]

Examples:
cCTAB_UTyte_2B_HI_LO --> 16
cCTAB_UTyte_1B_HI_LO --> 8
cCTAB_UTyte_3B_HI_LO --> 24
cCTAB_UTyte_4B_HI_LO --> 32
...

Is the lead in text always 12 characters long (as shown)? If so, you can use this slightly simpler formula...

=IFERROR(8*MID(A1,13,1),"")

If the lead in text's length can vary, then is the number always followed by the text "B_HI_LO"? If so, then this slightly simpler formula would work...

=IFERROR(8*MID(A1,LEN(A1)-7,1),"")

If neither of the above is true, then stay with the formula István posted.

 

[navic]

Read the 1st character after the second "_" (2) and multiply this value with 8.

Examples:
cCTAB_UTyte_2B_HI_LO
cCTAB_UTyte_1B_HI_LO
cCTAB_UTyte_3B_HI_LO
cCTAB_UTyte_4B_HI_LO

another way

=LEFT(RIGHT(A1,8),1)*8
=LEFT(RIGHT(A1,8),1)*16
....

 

[sleepdoc]

With data in A1 try

=TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(A1)," ",REPT(" ",60)),120),60))

Barry,

Can you please modify this to find the THIRD (not second) to last word?

Regards,

Glenn

 

[Scott Huish]

=trim(left(right(" "&substitute(trim(a1)," ",rept(" ",60)),180),60))

 

[saxet]

I love this concise solution but I'm not sure I understand it:

With data in A1 try

=TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(A1)," ",REPT(" ",60)),120),60))

Can someone tell me how this would look to extract the third to last word?

Thanks in advance!

 

[Scott Huish]

The 120 in the formula is 60x2 (the 2nd to the last word), so change it to 180 (3rd to the last word)
=TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(A1)," ",REPT(" ",60)),180),60))