Contributor

# Left or trim function with IF

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

# Re: Left or trim function with IF

Try this:

``=SUBSTITUTE(A2,".01","")``

# Re: Left or trim function with IF

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

# Re: Left or trim function with IF

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

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

# Re: Left or trim function with IF

@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

# Re: Left or trim function with IF

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

# Re: Left or trim function with IF

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.

# Re: Left or trim function with IF

Thanks guys
Really appreciate the help.