Numbers separate by commas read as thousands

I have ONE cell with various numbers/text that users get to input via a form. The input varies from all numbers separated by commas or can have some text as well.

If it is just numbers separated by commas and there are no spaces in between those numbers and commas then cell is read as number. I need some sort of a way / helper column to separate them out into one string that is comma separated and also has spaces not to be mistaken to thousands.

Here is a sample data:

Column Name: Ad ID(s)

row 1: 705,4,7892

row 2: 5

row 3: P35622, 722

As you can see the entries varies significantly from row to row.

How do I consistency "read" them as number/text, number/text, number/text

I tried using this formula =SUBSTITUTE([AD ID(s)]@row, ",", CHAR(10)) and while the output looks correct in Smartsheet, if I try to copy from this multiple picklist cell, pasting to my code gives me this "text", e.g.:

"705

7892"

Which does not save any time for the users that need these ad ids grabbed and placed in their codes without any further manipulation. Any thoughts how this can be handled?

P.S. It seems like you still have not fixed your tagging, lol:

Find more posts tagged with

Formulas

functionality

Accepted answers

Paul Newcome

Ah. I see now. Sorry about that. It looks to me like you are going to be limited to adding help text to the form and retraining everyone.

You can set up a helper column (flag type for example) that will flag any instances where there are not spaces entered after each comma by the users.

=IF(ISNUMBER([AC ID(s)]@row), 1)

Then set up an automatic update request to send to the submitter (or to yourself so you can reach out to them with a slight reminder) to fix it.

All comments

Genevieve P.

Hi @Ausrine Balletta

Would you be able to post a screen capture of your sheet/this column?

For the formula that you're using, are you entering this into a Multi-Select type of column so the values are separated?

You can turn numbers into text values by adding "" to them. For example, this should ensure that your values aren't appearing as thousands with a comma:

=[AD ID(s)]@row + ""

Let me know if that helped!

Cheers,

Genevieve

Ausrine Balletta

Hi @Genevieve P. ,

Thanks for responding.

Unfortunately, it did not work. Here is the screenshot, where Ad Id test is this multi-select column, and Ad Id test 2 is the formula suggested by you.

Any other suggestions?

Genevieve P.

Hi @Ausrine Balletta

Thank you for the screen capture!

You are correct, if there are no spaces between the comma and the next number then the cell will read that as part of a numerical value.

Instead of attempting to parse this out after the submission, would it be possible to identify in the form that there should be a space after any commas? This should let Smartsheet know to read the cell as containing multiple values.

Or perhaps we could change your helper formula to add a space instead of a character return (and put it in a Text/Num column instead of a multi-select):

=SUBSTITUTE([AD ID(s)]@row, ",", ", ")

Ausrine Balletta

Hi @Genevieve P. , appreciate you looking into this.

When you say to add this in the from, do you mean to provide a user instruction for the form submitting?

We might have issues with users reading that instruction now that they are very accustomed to this form and entries needed.

I tried this formula also in Text/Number column and still same thing.

@Paul Newcome or @Andrée Starå any thoughts?

Paul Newcome

If your SUBSTITUTE formula was working with the exception of how it appeared, swap the CHAR(10) out for a comma/space. CHAR(10) is a line break which is why the output looks the way it does. The other option would be to leave the CHAR(10) and just turn off text-wrapping.

Ausrine Balletta

Hi @Paul Newcome ,

That's what @Genevieve P. suggested to swap this:

=SUBSTITUTE([AD ID(s)]@row, ",", CHAR(10)) to this:

=SUBSTITUTE([AD ID(s)]@row, ",", ", ")

I just tried submitting a new form and it did not work with either of these formulas:

Is there a way to somehow restrict it on the form? Or maybe add a comma at the end of this Text/Number field before it shows up in the non-form view?

Paul Newcome

Ah. I see now. Sorry about that. It looks to me like you are going to be limited to adding help text to the form and retraining everyone.

You can set up a helper column (flag type for example) that will flag any instances where there are not spaces entered after each comma by the users.

=IF(ISNUMBER([AC ID(s)]@row), 1)

Then set up an automatic update request to send to the submitter (or to yourself so you can reach out to them with a slight reminder) to fix it.

Ausrine Balletta

Thank you @Paul Newcome ,

I guess this is going to be out option moving forward.

Thank you!

P.S. Thanks @Genevieve P. as well!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.