Nov 11 2022 06:03 AM
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
Nov 11 2022 06:13 AM
Nov 11 2022 06:37 AM
Nov 11 2022 06:43 AM
That wasn't clear from your first post. Here's the revision:
=INDEX(SORT(SUBSTITUTE(A1,"."&TEXT(SEQUENCE(10),"00"),"")),1)
Nov 11 2022 06:56 AM - edited Nov 11 2022 06:58 AM
@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
Nov 11 2022 06:56 AM
Nov 11 2022 07:09 AM
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.