Dec 28, 2022 11:29 AM
I have a cell containing values that are semi column separated and have a value after the :
How can I split this info into multiple cells?
|1||location A:1,Location B:3, Location C:9|
|2||location A:4,Location B:10, Location C:44|
I know all Locations, so I can pre-create all needed columns if this would be necessary (beter if it can be done automated of course).
How can I automatically copy the info to the right columns?
So needed output
|ID||Location A||Location B||Location C|
Any idea how to achieve this?
Dec 28, 2022 05:16 PM
I think you'll need three formulas. The assumption here is there will be no Location D!
Dec 29, 2022 12:29 AM
There can be many locations up to a couple hundreds.
And the locations are not on every line on the same place. I can have a line with location D,J,X and one J,F,X so the output of every line is different.
But in theorie i could replicate this formule a hundred times onces in my setup? Or is there a limitation
Dec 29, 2022 01:56 PM
It will be a problem as the last location does not have a comma to search for. Can you provide more examples of what the data looks like?
Dec 29, 2022 02:56 PM
here you go
14033 - SM Charles Quint:1;14084 - SM Nivelles:1;Ninove:3;Zellik – A :2;14161 - SM Kraainem:1;14013 - SM Seraing:1;14800 - Store candidates:2;Zellik – D :2;14057 - SM De Fre:1;Kobbegem - Hoofdzetel / Siège Social:48;Osseghem - Hoofdzetel / Siège Social:1;Zellik – E :10;14068 - SM Wondelgem:1;14075 - SM Ronse:1;Zellik - Transport:1;14130 - SM Westland:1;14048 - SM Leopold III:1
64791 - SM Pommerloch:1;64706 - SM Belval:2;Zellik – D :1;64707 - SM Schengen:1;Luxembourg - Hoofdzetel / Siège Social :2;64703 - SM Walferdange:1
Dec 30, 2022 01:26 AM
ok that's a bit different. With the location name not being in the same place all the time you'll need a script to run that can use a regex expression. I'll have a think about it but if anyone else has already done something similar feel free to jump in!