Multiple columns with alphanumeric data, standardize units.

[271828]

I have 9 columns of data that look like this. Ultimately I need everything in each column to have a standard unit (probably Gb) and be just a clean number.

Is there an easier way to do this than?

1. Create 9 helper columns via [9] Right formulas extracting last 4 characters
2. Clean the original columns of all whitespace and letters, convert to a numeric data type
3. With another 9 formulas, standardize the original columns based on whether the helper columns are "Gbps" or "Mbps".
4. Remove the helper columns.

I'm already like 30% through the above steps, so I'm wondering more for future reference if there's a more efficient method.

[Jonathan-Sherman · Accepted solution]

Hi @271828,

Yes this could be done with one multi-field formula (the same tool icon as your community avatar!) which would allow you to apply a formula to multiple columns at once.

IF Right(ToString([_CurrentField_]), 4) = 'Gbps'
THEN ToNumber(Regex_Replace(ToString([_CurrentField_]),'[^0-9.]','')) * 1000
ELSE ToNumber(Regex_Replace(ToString([_CurrentField_]),'[^0-9.]',''))
ENDIF

My example input:

Output after multi-field formula:

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[271828]

Well that was simple. Thanks! For what it's worth that avatar was the default- it'd be mildly amusing if I set that myself.