Forum Discussion

Qrew Trainee

3 years ago

Formula help to trim my numbers seperated by a comma

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

Qrew Cadet

3 years ago

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

KathyBenjamin
Qrew Trainee
3 years ago
Thank you. That worked awesome.

------------------------------
Kathy Benjamin
------------------------------
KathyBenjamin
Qrew Trainee
3 years ago
Is it possible to add to the formula to remove the zero when it is present?

0176771, 0176772, 0835409

example

------------------------------
Kathy Benjamin
------------------------------
- PrashantMaheshw
  Qrew Captain
  3 years ago
  You can check the condition with Left and then notleft
  
  if(left(Part($ControlNew, 1, ";"),1)="0",NotLeft(Part($ControlNew, 1, ";"),1),Part($ControlNew, 1, ";"))
  
  ------------------------------
  Prashant Maheshwari
  ------------------------------
  - MarkShnier__You
    Qrew Legend
    3 years ago
    ... 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
    ------------------------------