Comma separated field return value

[TomSpencer]

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?

ID	Locations
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
1	1	3	9
2	4	10	44

Any idea how to achieve this?

[John_B2]

I think you'll need three formulas. The assumption here is there will be no Location D!

Location A

MID(Locations,
FIND("A:", Locations,1)+2, 
FIND(",",Locations)-FIND(":",Locations)-1
)

Location B

MID(

  Locations,

  FIND("B:", Locations,1)+2,

  FIND(",",Locations, FIND("B:", Locations))-(FIND("B:",Locations)+2)

  )

Location C

RIGHT(Locations,
LEN(Locations)-FIND("C:",
Locations)-1
)

[TomSpencer]

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

[John_B2]

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?

[TomSpencer]

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

[John_B2]

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!