Split text in a cell to other cells

Options

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.



Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Options

    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
    Carson Penticuff ✭✭✭✭✭✭
    Options

    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
    Options

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

  • Katrina Walker
    Options

    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), "")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!