Left or trim function with IF

Brass Contributor

Is there a formula i can do save time avoid doing manually.
I have List of codes which have numbers

like following:

J975

K414

J993

K206

J890.01

J977

K138

K174.01


What i am trying to do is take away the “.01” from the codes that have this, and leaving the ones in tact that dont have it 

Is there a formula where i can do this ?

 

many thanks in advance

7 Replies

@excel_learner 

Try this:

=SUBSTITUTE(A2,".01","")
Thank you for this, but what if there are codes with .02 ,.04 and 0.6 so all different kinds how would a formula remove all

@excel_learner 

That wasn't clear from your first post. Here's the revision:

=INDEX(SORT(SUBSTITUTE(A1,"."&TEXT(SEQUENCE(10),"00"),"")),1)

@Patrick2788 alternatively:

 

=TEXTBEFORE(A1:A100,".",,,1)

or if you have an old version of excel

=LEFT(A1,IFERROR(SEARCH(".",A1)-1,999))

and fill down

 

 

 

Thank you thats brilliant.
How would you expand this as in if i had .0 or .1 or .00 in my list as well

@excel_learner 

This covers from ".00" to ".99":

=INDEX(SORT(SUBSTITUTE(A1,"."&TEXT(SEQUENCE(100,,0),"00"),"")),1)

 

A simplified formula may work, too:

=IFERROR(REPLACE(A1,FIND(".",A1),3,""),A1)

 

Also, if you have TEXTBEFORE/AFTER you might consider @mtarler 's solution. You have a few options.

Thanks guys
Really appreciate the help.