Split text in a cell to other cells

Katrina Walker

I am trying to split text in a cell across two other cells so I can split the country name from the work being done. There are blank spaces in between each text which I was trying to use and I got as far as getting the country name out using LEFT which gave me a char count then FIND using the char count to pull the country name into a further cell but countries like New Zealand then only show New.

I could not get the latter part of the cell to work at all.

I do have similar formulas working on cells using a mix of FIND and MID but they have different deliminators and here I only have spaces.

I am happy to split out each word and then concetanate the non country text back into a cell.

I cannot change the format as it is used in other sheets in this format.

Any ideas appreciated. In Excel I would use text to columns.

Paul Newcome

This is going to have to be a relatively manual process if you cannot change the delimiter. Even in Excel I don't think text to columns gives the option to skip the delimiter on some rows and use the delimiter in other rows like that.

Katrina Walker

Thanks Paul. Yes I was thinking that due to the delimiter always being a space then much harder to do and more manual.

Carson Penticuff

I may have a solution.

This assumes your country+work cells will always be on a second level hierarchy and that they always have a level one cell with the country name.

This would go into the Country column:

=IF(COUNT(ANCESTORS()) = 0, PARENT([Primary Column]@row), "")

And this would go into the work completed column:

=IF(COUNT(ANCESTORS([Primary Column]@row)) = 1, MID([Primary Column]@row, LEN(PARENT([Primary Column]@row)) + 2, LEN([Primary Column]@row) - LEN(PARENT([Primary Column]@row)) - 2), "")

Katrina Walker

Carson, yes they do! Thank you i am going to try this tomorrow morning.

Katrina Walker

Carson, thank you thank you thank you! I got this working! Had to make a wee tweak as per below but without your knowledge would have been nowhere near!

=PARENT([Country and Template Release]@row

=IF(COUNT(ANCESTORS([Country and Template Release]@row)) = 1, MID([Country and Template Release]@row, LEN(PARENT([Country and Template Release]@row)) + 2, LEN([Country and Template Release]@row) - LEN(PARENT([Country and Template Release]@row)) - 1), "")