Need formual to trim off all after the 2nd letter L

[tuongtu3]

Hi,
Please help me with formula how to trim the number after the 2nd letter L:

BEFORE
C33:L36
C52:L56L60
C71:L76L83L90
C103:L123L128L133L145

AFTER
C33:L36
C52:L56
C71:L76
C103:L123

Regards,
tt3

[Bernie Deitrick]

For values starting in A2:

=IFERROR(LEFT(A2,FIND("L",A2,FIND("L",A2)+1)-1),A2)

[ChemistB]

Try, with your text in A2,

Please Login or Register  to view this content.

[tuongtu3]

Hi,
The two formula work great but I want the result is blank if cell is blank. Please help me

Regards,
tt3

[Bernie Deitrick]

=if(a2="","",iferror(left(a2,find("l",a2,find("l",a2)+1)-1),a2))

[tuongtu3]

Hi Bernie,
Thank for your help

Regards,
tt3

[Rick Rothstein]

=if(a2="","",iferror(left(a2,find("l",a2,find("l",a2)+1)-1),a2))

I know this thread has been marked SOLVED, but I thought I would point out that your formula can be simplified like this...

=LEFT(A2,SEARCH("L",A2&"LL",SEARCH("L",A2&"L")+1)-1)

Note 1: I used SEARCH instead of FIND because the OP used upper case "L" in Message #1 but said your formula worked using lower case "L" in the FIND function. Since I was not sure what case the "L" actually is, I simply used SEARCH so it does matter. If the "L" is always upper case, the OP can replace my SEARCH functions with FIND if desired.

Note 2: The key being able to eliminate the IFERROR and test for "" is simply making sure the SEARCH (or FIND) function always has an "L" to find.