Substitute / or

Mary Kam

=SUBSTITUTE([CASE 1]@row, ",", CHAR(10))

Sometimes in the Case 1 cell, staff will enter ex 1, 2 or 1 - 2.

How would I add a or to substitute either of the scenarios. Thanks

ericncarr

Hi @Mary Kam here's a formula that will substitute the "," or the "-" and separate the integers, it will also separate the integers if they separate them with a space (so if they put "13 14" it will show two distinct values just like for the others ("13" and "14").

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Case 1]@row, "-", CHAR(10)), ",", CHAR(10)), " ", CHAR(10))

Andrée Starå

Hi @Mary Kam

I hope you're well and safe!

How would you like it to show? Can you add an example?

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Mary Kam

Mike TV

@Mary Kam

Hi Mary. It looks like you're using a multiselect dropdown column. Instead of writing a formula to correct how people are entering things in this column, why not turn on the "Restrict to drop down choices" option for the column? Down the road someone's going to think of a new way to break your column and you'll have to append your formula to fix that new problem. If you restrict the column to just the choices in the drop down then you don't have to account for how people break your sheet repeatedly.

Mary Kam

=SUBSTITUTE([CASE 1]@row, ",", CHAR(10))

I really want a formula that can separate the numbers if they put a , or -

Currently when they put a - the end result is ex: 11-12 so the next formula will count this item as 1 when it really is 2 items.

Restricting the columns did not work, but thanks for the suggestion.

ericncarr

Hi @Mary Kam here's a formula that will substitute the "," or the "-" and separate the integers, it will also separate the integers if they separate them with a space (so if they put "13 14" it will show two distinct values just like for the others ("13" and "14").

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Case 1]@row, "-", CHAR(10)), ",", CHAR(10)), " ", CHAR(10))

Mary Kam

Thank you so much., it work perfectly.