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.

 

[mozjesse]

That's quite handy.

Thanks!

Jesse

 

[7petro]

Hi,
how does it work with underlines in a string and with a different string length?

Examples:
nO_m10m_p10
nO_Unit_uNite2_m0m_p10
nO_uNite2_0_p10
nO_Unit_uNite2_-273,14_p10


The expected result should be like that:
m10m
m0m
0
-273,14

I would prefer an excel functions instead of a VBA script.

Thanks in advance!

That's quite handy.

Thanks!

Jesse

 

[István Hirsch]

Give this a try:

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1,"_",REPT(" ",200)),400),200))

 

[Rick Rothstein]

Hi,
how does it work with underlines in a string and with a different string length?

Examples:
nO_m10m_p10
nO_Unit_uNite2_m0m_p10
nO_uNite2_0_p10
nO_Unit_uNite2_-273,14_p10


The expected result should be like that:
m10m
m0m
0
-273,14

I would prefer an excel functions instead of a VBA script.

If you filled cells will always have at least one underline, then you can use this...

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1,"_",REPT(" ",100)),200),100))

If some of your filled cells might not have an underline (so there is not next-to-last "field"), then it depends on if you want the text returned (even though it is not the next-to-last word) or if you want a blank returned. If you want the word returned, then use the above formula, but if you want a blank returned, then use this formula instead...

=TRIM(LEFT(RIGHT(SUBSTITUTE("_"&A1,"_",REPT(" ",100)),200),100))

 

[7petro]

Hi Isvtàn, hi Rick,

thanks a lot for the fast response and finally for the function- it worked out immediately!

All the best!

If you filled cells will always have at least one underline, then you can use this...

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1,"_",REPT(" ",100)),200),100))

If some of your filled cells might not have an underline (so there is not next-to-last "field"), then it depends on if you want the text returned (even though it is not the next-to-last word) or if you want a blank returned. If you want the word returned, then use the above formula, but if you want a blank returned, then use this formula instead...

=TRIM(LEFT(RIGHT(SUBSTITUTE("_"&A1,"_",REPT(" ",100)),200),100))

 

[István Hirsch]

You are welcome.

 

[7petro]

Sorry for bothering again, but I am a bit confused with Excel now.

The following formula has been working well in my xslx worksheet. I safed my worksheed then to xls and when I opened today, I'v got an error message:

Formula:
=IF((LEFT(RIGHT(AH19,8),2))="1B","8",IF((LEFT(RIGHT(AH19,8),2))="2B","16",IF((LEFT(RIGHT(AH19,8),2))="3B","24",IF((LEFT(RIGHT(AH19,8),2))="4B","32",IF((LEFT(RIGHT(AH19,8),2))="5B","40",IF((LEFT(RIGHT(AH19,8),2))="6B","64",IF((LEFT(RIGHT(AH19,8),2))="TA",AV19,"---")))))))

Error message:
"The specified formula cannot be entered because it used more level of nesting than are allowed in the current file format.

Afterwards I saved in xlsx format again, but the error message still appears. Any help to solve these problem is much appreciated.

BUT - there is probably a better alternative method. Here an explanation for what I need the formula:

Cell AH19 contains: cCTAB_UTyte_2B_HI_LO

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

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

Thanks in advance!

 

[István Hirsch]

Give this a try:

=IFERROR(MID(A1,FIND("_",A1,FIND("_",A1)+1)+1,1)*8,"")

or, if you do not have Iferror function:

=MID(A1,FIND("_",A1,FIND("_",A1)+1)+1,1)*8

 

[7petro]

Give this a try:

=IFERROR(MID(A1,FIND("_",A1,FIND("_",A1)+1)+1,1)*8,"")

or, if you do not have Iferror function:

=MID(A1,FIND("_",A1,FIND("_",A1)+1)+1,1)*8

Fantastic !
The first approach with formula one just worked as it should.

Thanks a lot!

 

[István Hirsch]

You are welcome.