Skip to main content

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?

IDLocations
1location A:1,Location B:3, Location C:9
2location 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

IDLocation ALocation BLocation C
1139
241044

Any idea how to achieve this?

 

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
)

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
)

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

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


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?

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?


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

 

 

 

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

 

 

 


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!

Reply


Terms & Conditions