Extract data from one cell into another, but FIND character is one of two choices

[jblyx002]

I'm trying to extract everything after the L or R (at the end) of the cell below into another cell.

Column D GL 480+32.2L32 GL 489+01.3L32.1 GL 2+00R32 GL 3+00R32.5 so that Column E will show L32 L32.1 R32 R32.5 <tbody> </tbody><colgroup><col></colgroup>

<tbody>
</tbody><colgroup><col></colgroup>

 

[JoeMo]

Copy the formula in B1 down.

Excel Workbook
	A	B
1	GL 480+32.2L32	L32
2	GL 489+01.3L32.1	L32.1
3	GL 2+00R32	R32
4	GL 3+00R32.5	R32.5
Sheet3

 

[Momentman]

Please try this

Sub testedmacro()
    lastrow = Range("A1").End(xlDown).Row
    For I = 2 To lastrow
        If InStr(1, Cells(I, "D"), "L") > 0 Or InStr(1, Cells(I, "D"), "R") > 0 Then
            a = InStrRev(Cells(I, "D"), "L", -1)
            b = InStrRev(Cells(I, "D"), "R", -1)
            If a > b Then
                Cells(I, "E") = Right(Cells(I, "D"), Len(Cells(I, "D")) - a + 1)
            ElseIf a < b Then
                Cells(I, "E") = Right(Cells(I, "D"), Len(Cells(I, "D")) - b + 1)
            Else
                Exit Sub
            End If
        End If
     Next I       
            
End Sub

 

[Aladin Akyurek]

E2, control+shift+enter, not just enter:

=REPLACE(D2,1,MATCH(9.99999999999999E+307,MATCH(MID(D2,ROW(INDIRECT("1:"&LEN(D2))),1),{"L","R"},0))-1,"")

as another option.

 

[István Hirsch]

Give this formula a try, enter it into E1 and copy down. Do not use the formula if the number to extract contains leading zero(s).

=RIGHT(D1,LEN(LOOKUP(10^9,--RIGHT(D1,{1,2,3,4,5,6,7,8,9})))+1)

Hope in your version the decimal separator will be full stop at the ride side of the table.

*	A	B
1	GL 480+32.2L32	L32
2	GL 489+01.3L32,1	L32,1
3	GL 2+00R32	R32
4	GL 3+00R32,545	R32,545
5	GL 3+00R32222,5	R32222,5

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Peter_SSs]

Possibly a very slight tweak on István's formula ..

=RIGHT(D2,LEN(LOOKUP(1,-RIGHT(D2,{1,2,3,4,5,6,7,8,9}))))

.. but note the previous qualification regarding leading zeros for the number following the L or R, and also ..
- that the trailing number is limited to 9 characters
- the characters after the L or R may not necessarily form a number (though they do in all the samples)


If any of those issues are possible, another option that would also cope with those is ..

=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(D2,"L",REPT(" ",100)&"L"),"R",REPT(" ",100)&"R"),100))

 

[cmduong_vn]

you can try
=IFERROR(RIGHT(A2,LEN(A2)-FIND("L",A2,4)+1),RIGHT(A2,LEN(A2)-FIND("R",A2,4)+1))

given all your text in column D starts from A1

 

[markmzz]

Another way:

Use Ctrl+Shift+Enter to enter the formula
 
=MID(D2,MAX(IF(MID(D2,ROW(INDIRECT("1:99")),1)={"L","R"},ROW(INDIRECT("1:99")))),99)

Markmzz

 

[Peter_SSs]

you can try
=IFERROR(RIGHT(A2,LEN(A2)-FIND("L",A2,4)+1),RIGHT(A2,LEN(A2)-FIND("R",A2,4)+1))

given all your text in column D starts from A1

Welcome to the MrExcel board!

If that idea works, and it very well might given the samples quoted, then this shorter version should also.

=MID(D2,FIND("R",SUBSTITUTE(D2,"L","R"),3),LEN(D2))

 

[István Hirsch]

Welcome to the MrExcel board!

If that idea works, and it very well might given the samples quoted, then this shorter version should also.

=MID(D2,FIND("R",SUBSTITUTE(D2,"L","R"),3),LEN(D2))

In that case I would use:

=REPLACE(D2,1,FIND("R",SUBSTITUTE(D2,"L","R"),3)-1,"")