CODEWITHSUNDEEP
excelexcel-formula
Vendor EagleLimited
Vendor EagleLimited

Reputation: 73

Long winded Excel formula needs simplifying

Is there a way I can simplify this formula?

=IFERROR(IF(LEN(RIGHT(K3,LEN(K3)-FIND("@",SUBSTITUTE(K3," ","@",LEN(K3)-LEN(SUBSTITUTE(K3," ",""))))))<4,IF(LEN(RIGHT(K3,LEN(K3)-FIND("@",SUBSTITUTE(K3," ","@",LEN(K3)-LEN(SUBSTITUTE(K3," ",""))))))< 3,IF(LEN("00"&LEFT(RIGHT(K3,2),1)+1&"l")>4,"0"&LEFT(RIGHT(K3,2),1)+1&"l","00"&LEFT(RIGHT(K3,2),1)+1&"l"),IF(LEN("0"&LEFT(RIGHT(K3,3),2)+1&"l")<4,"00"&LEFT(RIGHT(K3,3),2)+1&"l","0"&LEFT(RIGHT(K3,3),2)+1&"l")),IF(LEN(LEFT(RIGHT(K3,4),3)+1&"l")<4,IF(LEN(LEFT(RIGHT(K3,4),3)+1&"l")< 3,"00"&LEFT(RIGHT(K3,4),3)+1&"l","0"&LEFT(RIGHT(K3,4),3)+1&"l"),(LEFT(RIGHT(K3,4),3)+1&"l"))),IF(LEN(K3)<4,IF(LEN(K3)< 3,IF(LEN("00"&LEFT(K3,1)+1&"l")>4,"0"&LEFT(K3,1)+1&"l","00"&LEFT(K3,1)+1&"l"),IF(LEN("0"&LEFT(K3,2)+1&"l")<4,"00"&LEFT(K3,2)+1&"l","0"&LEFT(K3,2)+1&"l")),IF(LEN(LEFT(K3,3)+1&"l")< 3,"00" & LEFT(K3,3)+1&"l", IF(LEN(LEFT(K3,3)+1&"l")<4,"0"&LEFT(K3,3)+1&"l",LEFT(K3,3)+1&"l"))))

It had to find the last number in the cell, add 1 to it and put an a L at the end. If the number was under 3 digits then 0's would have to be added before the number (083, 071, 043, 005, 002 etc).

Input          - Output

1L             - 002L
03R            - 004L
483L           - 484L
232R 233L 234L - 235L
08L 009L       - 010L
4L 005R 6R     - 007L
89L 90R 91L    - 092L

Upvotes: 7

Views: 104

Answers (3)

Scott Craner
Scott Craner

Reputation: 152505

Another one using FILTERXML:

=TEXT(FILTERXML("<t><s>"&SUBSTITUTE(LEFT(K3,LEN(K3)-1)," ","</s><s>")&"</s></t>","//s[last()]")+1,"000")&"L"

enter image description here

Upvotes: 6

Ross
Ross

Reputation: 83

I have the tested the following and I think it fulfills your criteria

=TEXT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(B3," ",REPT(" ",100)),100)), RIGHT(B3, 1), "")+1, "000")&"L"

Upvotes: 5

Rory
Rory

Reputation: 34045

This works for your examples:

=TEXT(-LOOKUP(2,-MID(TRIM(RIGHT(SUBSTITUTE(K3," ",REPT(" ",1000)),1000)),1,ROW($1:$15)))+1,"000L")

Upvotes: 6

Related Questions