Extract a particular characters from a cell

[DQ2013]

Hi Can some one please help me on the following. I have the following data , I only require the first letter after the 2nd dash if the letter is L or R or S and if the length of the characters after the second dash is 4

[TABLE="class: grid, width: 239"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Data[/TD]
[TD]Require[/TD]
[/TR]
[TR]
[TD]BW1550T-NAV-L084[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]BW1550T-NAV-L089[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]BW1550T-NAV-R097[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]BW1550T-NAV-R102[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]BW1550T-NAV-S117[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]BW1550T-NAV-S122[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]CFPR180-CS[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CFPR180-DM[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CFPR180[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]DTW1150-NAV-ZBULK[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]DTW1150-NAV-12[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]DTW1150-NAV-14[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]GFPR100-CS[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]GFPR100-DM[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]GFPR105-CS[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]GFPR105-DM[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BW1550T-NAV-R097[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]BW1550T-NAV-S117[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]DS2166T1-O/N-AXXS[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]DS2166T1-O/N-BXS[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]DS2166T1-O/N-CS[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]DS2166T1-O/N-DM[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]DT1138T-NAV-R127[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]DT1138T-NAV-S087[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]DT1138T-NAV-S092[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]DT1138T-NAV-S097[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]DT1138T-NAV-S102[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]DT1138T-NAV-S107[/TD]
[TD]S[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

I believe this slightly shorter formula will also work...

That comes back partly to the interpretation of "characters after the second dash" per my previous post.

It also produces an error for the OP's sample data
CFPR180

.. and, if more than 2 dashes are possible, produces an incorrect result (by my interpretation) for
X-4-LBBB-G

The OP seems satisfied.

 

[Peter_SSs]

The length of the characters after the second dash is 4 for this...

X-4-L-GG

Literally speaking, yes. But if dash is a delimiter, I see that after the 1st dash there is 1 character, after the 2nd is 1 character etc. The OP seems satisfied.

 

[Rick Rothstein]

It also produces an error for the OP's sample data

CFPR180

This fixes the above problem...

=IF(AND(FIND("-",A13&"--",FIND("-",A13&"-")+1)<LEN(A13)-3,OR(MID(TRIM(A13),LEN(TRIM(A13))-4,2)={"-L","-R","-S"})),LEFT(RIGHT(TRIM(A13),4)),"")

.. and, if more than 2 dashes are possible, produces an incorrect result (by my interpretation) for
X-4-LBBB-G

The OP said "...and if the length of the characters after the second dash is 4"... there are more than 4 characters after the second dash, so I interpret the OP's request as being that nothing should be displayed for it (your formula displays the "L").

 

[Rick Rothstein]

This fixes the above problem...

=IF(AND(FIND("-",A13&"--",FIND("-",A13&"-")+1)

I forgot about the HTML translator... here is the full formula...


=IF(AND(FIND("-",A13&"--",FIND("-",A13&"-")+1)<LEN(A13)-3,OR(MID(TRIM(A13),LEN(TRIM(A13))-4,2)={"-L","-R","-S"})),LEFT(RIGHT(TRIM(A13),4)),"")

 

[Peter_SSs]

I forgot about the HTML translator... here is the full formula...


=IF(AND(FIND("-",A13&"--",FIND("-",A13&"-")+1)<LEN(A13)-3,OR(MID(TRIM(A13),LEN(TRIM(A13))-4,2)={"-L","-R","-S"})),LEFT(RIGHT(TRIM(A13),4)),"")

As far as the evidence is so far, we are no longer dealing with the OP's data as
- there was no sample data with more than 2 dashes, &
- post 5

However
IF your interpretation was correct then this shorter formula seems to produce the result requested and does not return an incorrect "" for
BW1550T-NAV-L  D

& does not return an incorrect "L" for
BW1550T-NAV-L         4D

=IF(AND(LEN(REPLACE(A2,1,FIND("-",A2&"--",FIND("-",A2&"-")+1),""))=4,OR(LEFT(RIGHT(A2,4))={"L","R","S"})),LEFT(RIGHT(A2,4)),"")