Left or trim function with IF

Question

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

patrick2788 · Answer

excel_learner&nbsp;Try this:=SUBSTITUTE(A2,".01","")

excel_learner · Answer

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

patrick2788 · Answer

excel_learner&nbsp;That wasn't clear from your first post. Here's the revision:=INDEX(SORT(SUBSTITUTE(A1,"."&amp;TEXT(SEQUENCE(10),"00"),"")),1)

mtarler · Answer

Patrick2788&nbsp;alternatively:&nbsp;=TEXTBEFORE(A1:A100,".",,,1)or if you have an old version of excel=LEFT(A1,IFERROR(SEARCH(".",A1)-1,999))and fill down&nbsp;&nbsp;&nbsp;

excel_learner · Answer

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

Forum Discussion

Left or trim function with IF

7 Replies

Resources