IF Help (Second last chracter in cell matches)

[Mr. Alex]

Howdy,

I have a column of text like this

F26139
J28661
D798
D29766
D16842_A
D16842_B
D16842_C
D16842_D
J29687_A
J29687_B
J29687_C
J29687_D
C353_A
C353_B
A16484
G28194
G20034
A14916
H18803_A
H18803_B
H18803_C
H18803_D


I need to remove the _A _B _C from the cell, only when present. My column is over 2000 rows long and the _A _B _C shows up everywhere... also, it spans the whole alphabet..

I figure some sort of IF statement would do it, but I need to isolate the underscore _ … It will always be the second last character in the cell.

Any ideas?

Thanks

Mr. Alex

 

[AlohaJulio]

Try something like
=IF(ISERR(FIND("_",A1)),A1,MID(A1,1,FIND("_",A1)-1))

 

[al_b_cnu]

Hi,

Another way is ...
=IF(MID("**"&A1,LEN("**"&A1)-1,1)="_",LEFT(A1,LEN(A1)-2),A1)

HTH

Alan

 

[Joe4]

Try:

=IF(MID(A1,LEN(A1)-1,1)="_",LEFT(A1,LEN(A1)-2),A1)

 

[onlyadrafter]

Hello,

Using a formula

=IF(ISERROR(FIND("_",A1)),A1,LEFT(A1,FIND("_",A1)-1))

or

have you tried find '_?' and replace with nothing?

 

[fairwinds]

=LEFT(A1,FIND("_",A1&"_")-1)

 

[just_jon]

Very well done, fairwinds!

 

[Joe4]

=LEFT(A1,FIND("_",A1&" _")-1)

WOW! Now that is a thing of beauty...

I salute you!

 

[Mr. Alex]

Holy cow, thats sweet man!

Thank you everyone for your help, and Fairwinds for the sweetest bit of code i've seen in quite some time!

I think Fairwinds, if you keep this up, you'll raise the bar for us all. :wink:


Mr. Alex