Formula help to trim my numbers seperated by a comma

Question

Hi,I have a field [Control Number] that has multiple controls listed and separated by a comma.&nbsp; I'm looking for a formula that will capture the control number without the comma.Ex[Control Number] = [187461, 187462, 187463, 187464, 187465, 187466]I need to break out by the following into 6 different fields:[Control 1] = 187461[Control 2] = 187462[Control 3] = 187463[Control 4] = 187464[Control 5] = 187465[Control 6] = 187466Any ideas? Thank you in advance!------------------------------Kathy Benjamin------------------------------

tammieking · Answer

I think you need to change the separator before replacing the commas.
First replace the Comma+Space with a semicolon. Then replace the commas.Try this for Control1:
var text ControlTemp = SearchAndReplace([Control],", ",";");var text ControlNew = SearchAndReplace($ControlTemp,",","");Part($ControlNew, 1, ";")------------------------------Tammie King------------------------------

markshnier__you · Answer

... an alternative to removing the possible leading 0 is to convert to a number and then back to text.var text ControlTemp = SearchAndReplace([Control],", ",";");var text ControlNew = SearchAndReplace($ControlTemp,",","");var text Control = Part($ControlNew, 1, ";");ToText(ToNumber($Control))------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

prashantmaheshw · Answer

There is a searchandreplace formula which should be relevant for youSearchandReplace([Control Number],",","")------------------------------Prashant Maheshwari------------------------------

kathybenjamin · Answer

Thank you, I'll check it out.------------------------------Kathy Benjamin------------------------------

kathybenjamin · Answer

Thank you.&nbsp; That worked awesome.------------------------------Kathy Benjamin------------------------------

Forum Discussion

Formula help to trim my numbers seperated by a comma

8 Replies