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]

Try

Excel Workbook
	A	B
1	Data	Require
2	BW1550T-NAV-L084	L
3	BW1550T-NAV-L089	L
4	BW1550T-NAV-R097	R
5	BW1550T-NAV-R102	R
6	BW1550T-NAV-S117	S
7	BW1550T-NAV-S122	S
8	CFPR180-CS
9	CFPR180-DM
10	CFPR180
11	DTW1150-NAV-ZBULK
12	DTW1150-NAV-12
13	DTW1150-NAV-14
14	GFPR100-CS
15	GFPR100-DM
16	GFPR105-CS
17	GFPR105-DM
18	BW1550T-NAV-R097	R
19	BW1550T-NAV-S117	S
20	DS2166T1-O/N-AXXS
21	DS2166T1-O/N-BXS
22	DS2166T1-O/N-CS
23	DS2166T1-O/N-DM
24	DT1138T-NAV-R127	R
25	DT1138T-NAV-S087	S
26	DT1138T-NAV-S092	S
27	DT1138T-NAV-S097	S
28	DT1138T-NAV-S102	S
29	DT1138T-NAV-S107	S
Get Letter

 

[admiral100]

Hi,

Another way :

=IFERROR(IF(MATCH(TRUE,LEFT(RIGHT(A2,5),2)={"-L","-R","-S"},0)>0,LEFT(RIGHT(A2,4),1),""),"")

 

[Peter_SSs]

Another way :

=IFERROR(IF(MATCH(TRUE,LEFT(RIGHT(A2,5),2)={"-L","-R","-S"},0)>0,LEFT(RIGHT(A2,4),1),""),"")

That wouldn't work for values like
CFPR180-LXCS
X-L-GG

 

[DQ2013]

Thank you works beautifully.

 

[Peter_SSs]

Thank you works beautifully.

You're welcome.

 

[Rick Rothstein]

Spreadsheet Formulas

Cell Formula B2 =IF(AND(LEN(TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",50)),100,50)))=4,OR(LEFT(TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",50)),100,50)),1)={"L","R","S"})),LEFT(TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",50)),100,50)),1),"") <tbody> </tbody>

<tbody>
</tbody>

Peter, your formula does not return the letter for this kind of construction...

X-4-L-GG

I do not see the OP's description disallowing that (plus if you replace the third dash with any other non-alphanumeric character, your formula will return the letter).

 

[Peter_SSs]

Peter, your formula does not return the letter for this kind of construction...

X-4-L-GG

I do not see the OP's description disallowing that (plus if you replace the third dash with any other non-alphanumeric character, your formula will return the letter).

Perhaps you interpreted this differently to me.

.. if the length of the characters after the second dash is 4

Additionally, there was no sample data with 3 dashes. The OP seems happy.

 

[Rick Rothstein]

Spreadsheet Formulas

Cell Formula B2 =IF(AND(LEN(TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",50)),100,50)))=4,OR(LEFT(TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",50)),100,50)),1)={"L","R","S"})),LEFT(TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",50)),100,50)),1),"") <tbody> </tbody>

<tbody>
</tbody>

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

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

 

[Rick Rothstein]

Perhaps you interpreted this differently to me.

Quote Originally Posted by DQ2013 View Post
.. if the length of the characters after the second dash is 4

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

X-4-L-GG