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

jblyx002

New Member
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
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

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

MrExcel MVP
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
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

Last edited:

Peter_SSs

MrExcel MVP, Moderator
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
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
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
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
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,"")