Skip to main content
Question

Formula help to trim my numbers seperated by a comma

  • Aug 18, 2022 8:54 PM UTC
  • 0 replies
  • 184 views

Forum|alt.badge.img+5
Hi,

I have a field [Control Number] that has multiple controls listed and separated by a comma.  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] = 187466

Any ideas? Thank you in advance!

------------------------------
Kathy Benjamin
------------------------------
This topic has been closed for replies.

Forum|alt.badge.img+11
There is a searchandreplace formula which should be relevant for you

SearchandReplace([Control Number],",","")

------------------------------
Prashant Maheshwari
------------------------------

TammieKing
Forum|alt.badge.img+10
  • Qrew Corporal
  • Aug 19, 2022 12:49 PM UTC

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
------------------------------