Column A lists the categories for individual products. Each product will have between 1 to 14 categories. The goal is to split all categories into separate cells (Columns B through O), to become easier to sort.
I've created formulas for columns B through O, to SEARCH for hyphens "-" and separate each category into its own column. Here's what the output should look like (except for the bottom row):
A B C D E F G ... O
Categories Cat 1 Cat 2 Cat 3 Cat 4 Cat 5 Cat 6 ... Cat 14
UX UX
CFT-WET CFT WET
WEM-US-CFT WEM US CFT
NC-US-CFT NC US CFT
TP-OB-SB-WEB TP OB SB WEB
DB-B-FC DB B FC
P-TP-SB-CP-DT P TP SB CP DT
DP-S-OB-WB-SB-FC DP S OB WB SB FC
P-TP-SB-CP-WEB-WS-S-TP-OB-C-CT-G-FC-MCB
I initially built these formulas:
Col B: =if(iserror(search("-",$A5)),$A5,search("-",$A5))
Col C: =if(iserror(search("-",$A5,sum(search("-",$A5)+1))),"",mid($A5,sum(search("-",$A5),1),sum(search("-",$A5,sum(search("-",$A5)+1)),-search("-",$A5),-1)))
Col D: =if(iserror(search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)),"",mid($A5,sum(search("-",$A5,search("-",$A5)+1),1),sum(search("-",$A5,search("-",$A5,search("-",$A5)+1)+1),-search("-",$A5,search("-",$A5)+1),-1)))
Col E: =if(iserror(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)),"",mid($A5,sum(search("-",$A5,search("-",$A5,search("-",$A5)+1)+1),1),sum(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1),-search("-",$A5,search("-",$A5,search("-",$A5)+1)+1),-1)))
Col F: =if(iserror(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)),"",mid($A5,sum(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1),1),sum(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1),-search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1),-1)))
Col G: =if(iserror(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)),"",mid($A5,sum(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1),1),sum(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1),-search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1),-1)))
Col H: =if(iserror(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1)),"",mid($A5,sum(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1),1),sum(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1),-search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1),-1)))
Col I: =if(iserror(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1)+1)),"",mid($A5,sum(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1),1),sum(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1)+1),-search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1),-1)))
Col J: =if(iserror(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1)+1)+1)),"",mid($A5,sum(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1)+1),1),sum(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1)+1)+1),-search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1)+1),-1)))
Col K: =if(iserror(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1)+1)+1)+1)),"",mid($A5,sum(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1)+1)+1),1),sum(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1)+1)+1)+1),-search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1)+1)+1),-1)))
Col L: =if(iserror(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)),"",mid($A5,sum(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1)+1)+1)+1),1),sum(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1),-search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1)+1)+1)+1),-1)))
Col M: =if(iserror(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)),"",mid($A5,sum(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1),1),sum(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1),-search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1),-1)))
Col N: =if(iserror(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)),"",mid($A5,sum(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1),1),sum(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1),-search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1),-1)))
Col O: =if(iserror(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)),"",mid($A5,sum(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1),1),sum(search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1),-search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1),-1)))
...but these fail to recognize the final category for each row. Col B's formula has no problems, so I began to edit the formulas in Columns C to O:
Col C: =if(iserror(search("-",$A5,sum(search("-",$A5)+1))),if($B5<>"",right($A5,sum(len($A5),-search("-",$A5))),""),mid($A5,sum(search("-",$A5),1),sum(search("-",$A5,sum(search("-",$A5)+1)),-search("-",$A5),-1)))
Col D: =if(iserror(search("-",$A5,search("-",$A5,search("-",$A5)+1)+1)),if($C5<>"",if(iserror(search("-",$A5,search("-",$A5)+1)),"",right($A5,len(sum($A5,-search("-",$A5,search("-",$A5)+1))))),""),mid($A5,sum(search("-",$A5,search("-",$A5)+1),1),sum(search("-",$A5,search("-",$A5,search("-",$A5)+1)+1),-search("-",$A5,search("-",$A5)+1),-1)))
It appears this solution will work for all situations except when Column A has only one category. When that happens, a #VALUE! error will populate all columns to the right of Col B. How do I solve this?
For now, I'm stuck with this:
A B C D E F G ... O
Categories Cat 1 Cat 2 Cat 3 Cat 4 Cat 5 Cat 6 ... Cat 14
UX UX #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! ... #VALUE!
CFT-WET CFT WET
WEM-US-CFT WEM US CFT
NC-US-CFT NC US CFT
TP-OB-SB-WEB TP OB SB WEB
DB-B-FC DB B FC
P-TP-SB-CP-DT P TP SB CP DT
DP-S-OB-WB-SB-FC DP S OB WB SB FC
P-TP-SB-CP-WEB-WS-S-TP-OB-C-CT-G-FC-MCB
Aucun commentaire:
Enregistrer un commentaire