Forum Discussion

syazaaoa95's avatar
syazaaoa95
Brass Contributor
May 15, 2023
Solved

Can't I make the formula so that it will read in order?

Hi, I have another problem. which my problem is about, I want it to read on my first argument on the front, and after that it will read the arguments after that if the argument on the front ...
excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    May 16, 2023

    syazaaoa95 Perhaps not too complex after all.

     

    Begin by listing of all possible delimiters and create a named range of it. In the example I called it "delim".  Then the following formulas will create the LEFT and RIGHT segments.

     

    =TRIM(IFERROR(LEFT([@DESCRIPTION],MIN(IFERROR(FIND(delim,[@DESCRIPTION])-1,999999))),[@DESCRIPTION]))

     

    =TRIM(IFERROR(RIGHT([@DESCRIPTION],LEN([@DESCRIPTION])-MIN(IFERROR(FIND(delim,[@DESCRIPTION])-1,999999))),""))

     

    See attached.

     

HansVogelaar's avatar
HansVogelaar
MVP
May 15, 2023

syazaaoa95 

In C2:

 

=IFERROR(RIGHT(A2,LEN(A2)-(MIN(IFERROR(SEARCH("MXM",A2),9^99),IFERROR(SEARCH("MX7",A2),9^99),IFERROR(SEARCH("MX7",A2),9^99),IFERROR(SEARCH("MX1",A2),9^99),IFERROR(SEARCH("MX2",A2),9^99),IFERROR(SEARCH("MX4",A2),9^99),IFERROR(SEARCH("MX3",A2),9^99),IFERROR(SEARCH("DX",A2),9^99),IFERROR(SEARCH("MX5",A2),9^99),IFERROR(SEARCH("MX6",A2),9^99),IFERROR(SEARCH("MXB",A2),9^99),IFERROR(SEARCH(" AL",A2),9^99)+1,IFERROR(SEARCH("ARM",A2),9^99),IFERROR(SEARCH(" ARM",A2),9^99)+1,IFERROR(FIND(")",A2),9^99)+2,IFERROR(SEARCH("BP",A2),9^99),IFERROR(SEARCH("MXC",A2),9^99),IFERROR(SEARCH(" SF",A2),IFERROR(FIND("AR",A2),9^99)+2)-2)-1)),"")

 

Fill down.

  • syazaaoa95's avatar
    syazaaoa95
    Brass Contributor
    May 15, 2023
    Hi Hans,

    Thank you so much for giving me suggestion on how to make it in blank state.

Resources