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

jblyx002

New Member
Joined
Nov 30, 2012
Messages
8
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,669
Copy the formula in B1 down.
Excel Workbook
AB
1GL 480+32.2L32
2GL 489+01.3L32.1L32.1
3GL 2+00R32R32
4GL 3+00R32.5R32.5
Sheet3
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
3,963
Please try this

Code:
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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

Well-known Member
Joined
May 16, 2013
Messages
1,634
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.

*AB
1GL 480+32.2L32L32
2GL 489+01.3L32,1L32,1
3GL 2+00R32R32
4GL 3+00R32,545R32,545
5GL 3+00R32222,5R32222,5

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,327
Office Version
365
Platform
Windows
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))
 
Last edited:

cmduong_vn

New Member
Joined
Feb 9, 2014
Messages
3
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

MrExcel MVP
Joined
May 7, 2011
Messages
3,699
Another way:

Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,327
Office Version
365
Platform
Windows
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

Well-known Member
Joined
May 16, 2013
Messages
1,634
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,"")
 

Forum statistics

Threads
1,082,638
Messages
5,366,699
Members
400,914
Latest member
anandkb

Some videos you may like

This Week's Hot Topics

Top