Forum Discussion

Brass Contributor

Nov 11, 2022

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 i...

excel

Formulas and Functions

Patrick2788

Silver Contributor

Nov 11, 2022

excel_learner

Try this:

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

excel_learner

Brass Contributor

Nov 11, 2022

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
Silver Contributor
Nov 11, 2022
excel_learner
That wasn't clear from your first post. Here's the revision:
=INDEX(SORT(SUBSTITUTE(A1,"."&TEXT(SEQUENCE(10),"00"),"")),1)
- excel_learner
  Brass Contributor
  Nov 11, 2022
  Thank you thats brilliant.
  How would you expand this as in if i had .0 or .1 or .00 in my list as well
  - Patrick2788
    Silver Contributor
    Nov 11, 2022
    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.
- mtarler
  Silver Contributor
  Nov 11, 2022
  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