Separating a series of number separated by spaces into multiple cells

sawuzie

Hello!

I have a Smartsheet grid that is updated via a form input. One of the columns, "Label ID", is dedicated to cell that contain a series of numbers that are separated by a space (10067 4866 48676 3857). These numbers can vary in length.

Is there a formula that can allow me to separate these numbers into separate cells? I have searched for solutions to this, but haven't had much luck. I have an entire grid that this single column will be cell linked to so I can be lenient in the formatting.

Thank you

Paul Newcome

Try something like this:

=MID("!" + [Label ID]@row + "!", FIND("!", SUBSTITUTE("!" + [Label ID]@row + "!", " ", "!", 1) + 1, FIND("!", SUBSTITUTE("!" + [Label ID]@row + "!", " ", "!", 2) - (FIND("!", SUBSTITUTE("!" + [Label ID]@row + "!", " ", "!", 1) + 1))

The bold numbers (inside each of the SUBSTITUE functions) would be updated to 2, 3, 2 for the second set then 3, 4, 3 for the third set, so on and so forth.

You can wrap the whole thing in an IFERROR if there can be a variable number of IDs within the cell.

=IFERROR(MID("!" + [Label ID]@row + "!", FIND("!", SUBSTITUTE("!" + [Label ID]@row + "!", " ", "!", 1) + 1, FIND("!", SUBSTITUTE("!" + [Label ID]@row + "!", " ", "!", 2) - (FIND("!", SUBSTITUTE("!" + [Label ID]@row + "!", " ", "!", 1) + 1)), "")

sawuzie

@Paul Newcome

Thank you for your response! Sorry, I'm not exactly sure what you mean by updating the bold number as I'm still new to Smartsheets, what are those number series in reference to?

My formula returned as "incorrect argument" (i'm assuming because I did not follow your directions fully). What formatting should I expect my data to return as?

Paul Newcome

That particular error is my mistake. I missed a couple of closing parenthesis.

=MID("!" + [Label ID]@row + "!", FIND("!", SUBSTITUTE("!" + [Label ID]@row + "!", " ", "!", 1)) + 1, FIND("!", SUBSTITUTE("!" + [Label ID]@row + "!", " ", "!", 2)) - (FIND("!", SUBSTITUTE("!" + [Label ID]@row + "!", " ", "!", 1)) + 1))

But having looked at it again I realize I need to make another adjustment.

=MID("!" + SUBSTITUTE([Label ID]@row, " ", "!") + "!", FIND("?", SUBSTITUTE("!" + SUBSTITUTE([Label ID]@row, " ", "!") + "!", "!", "?", 1)) + 1, FIND("?", SUBSTITUTE("!" + SUBSTITUTE([Label ID]@row, " ", "!") + "!", "!", "?", 2)) - ((FIND("?", SUBSTITUTE("!" + SUBSTITUTE([Label ID]@row, " ", "!") + "!", "!", "?", 1)) + 1))

The bold numbers in the formula indicate which entry you are tying to pull. Basically the start and stop for the MID function. So to pull the first entry, you start at the first delimiter and then to get the number of characters to pull for the MID function, we find the second delimiter and subtract the first delimiter's position from it.

FIND will output the character number based on where within a string that character is found.

FIND("!", "!rgsdrg")

will output 1 because the first character is the specified character.

FIND("!", "rgsd!rg")

will output 5 because now the specified character is the 5th in the string.

So to separate out the first entry, use the second formula above. To pull out the second string, you would adjust the bold numbers to 2, 3, and 2. Then to pull out the third you would adjust the numbers to 3, 4, and 3.

sawuzie

@Paul Newcome I was able to pull new cells with your formula, thank you so much!

My next question is: is there a way I can make this formula automated so that when a series of numbers is entered in a new row, I can use the formula to pull all numbers in the series into separate cells automatically? I know this may be a long shot. Typically these series can range from a list of 5-50 number groups in a single cell, so doing it manually would be slightly difficult.

Any ideas would appreciated, thank you.

sawuzie

I created 3 helper columns (First #, Second #, Third #) alongside the target series that are sequentially numbered so that they are applicable to the formula when referenced via "@row"

For example, the when three helper columns are | 2 | 3 | 2 |, this formula is used in the next column:

=MID("!" + SUBSTITUTE([Label ID]@row, " ", "!") + "!", FIND("?", SUBSTITUTE("!" + SUBSTITUTE([Label ID]@row, " ", "!") + "!", "!", "?", [First #]@row)) + 1, FIND("?", SUBSTITUTE("!" + SUBSTITUTE([Label ID]@row, " ", "!") + "!", "!", "?", [Second #]@row)) - ((FIND("?", SUBSTITUTE("!" + SUBSTITUTE([Impacted MED ID's]@row, " ", "!") + "!", "!", "?", [Third #]@row) + 1))))

This pulled the correct number in the series, however there is an exclamation point that follows each number. I guess I could use a formula to -1 but I'm sure there is a way to adjust the formula itself.

Am I on the right track?

sawuzie

@Paul Newcome sorry, not sure if you are alerted when I don't tag you ^^^

Paul Newcome

I do get alerted, but I also do not always respond immediately (2 jobs and a family).

I'm not sure I understand what you're doing with your helper columns. You should be able to just drop the formula into a cell to pull the first one. Then drop it in another cell and adjust the numbers to pull the second, so on and so forth until you've dropped it in enough cells to pull everything.

The reason you are getting the exclamation point is because your syntax is off. Just drop this into the first cell. If it is working, drop it into another and adjust the previously indicated numbers to 2, 3, 2. If that is working then drop it into another cell and adjust the numbers to 3, 4, 3.

=MID("!" + SUBSTITUTE([Label ID]@row, " ", "!") + "!", FIND("?", SUBSTITUTE("!" + SUBSTITUTE([Label ID]@row, " ", "!") + "!", "!", "?", 1)) + 1, FIND("?", SUBSTITUTE("!" + SUBSTITUTE([Label ID]@row, " ", "!") + "!", "!", "?", 2)) - (FIND("?", SUBSTITUTE("!" + SUBSTITUTE([Label ID]@row, " ", "!") + "!", "!", "?", 1)) + 1))

sawuzie

Apologies @Paul Newcome did not mean to come off impatient, just want to make sure I'm using Smartsheet Community correctly.

The formula you created worked, thank you

Paul Newcome

Happy to help. 👍️

And no worries. Sometimes I am able to respond pretty quickly, and if someone is new to the Community they may not realize that someone else may get a notification even without a "tag".

Unless someone "unsubscribes" from a thread, they will continue to receive alerts (based on their personal settings could be just here, in email, or both) whenever someone else comments on it even if they aren't tagged directly. I have even gotten alerts from someone commenting on a thread that had been stagnant for more than a year before.

sawuzie

That makes sense, thank you for your explanation!