4 Replies Latest reply on Aug 19, 2016 12:22 PM by Jennifer Martine

# REGEXP for alphanumeric then using formula for conversion.

Can anyone assist with creating a calculated field using regexp for an alphanumeric excel field that also needs to be converted to Miles if it is currently in Kilometers?

I've tried isolating the numbers first. Then isolating the letters. Then an IF/THEN calculation stating if M, then leave as is, but if K multiply .621. All my calculations are valid, yet now I have 4 separate calculated fields and the final one is all Null.

Anyone have another solution that using only one calculated field?

The excel cells look like:

96821 K

461894 K

1200 M

2631 K

• ###### 1. Re: REGEXP for alphanumeric then using formula for conversion.

Why use regex at all?

I would just use

IF CONTAINS([yourfield],"K")

THEN FLOAT(REPLACE([yourfield]," K","")) * .621

ELSEIF CONTAINS([yourfield],"M")

THEN THEN FLOAT(REPLACE([yourfield]," M",""))

END

2 of 2 people found this helpful
• ###### 2. Re: REGEXP for alphanumeric then using formula for conversion.

Jennifer!

I don't think you actually need a REGEXP() function. Just right click on the original field and perform a custom split. Then on the custom field you can create a calc like:

if [String - Split 2] = 'K' then [String - Split 1]*0.621 else [String - Split 1] end

Then you can right click and change the default format of this calculated field to have a suffix 'M'

2 of 2 people found this helpful
• ###### 3. Re: REGEXP for alphanumeric then using formula for conversion.

Thank you so much for your help! I'm learning more Tableau tricks everyday.

• ###### 4. Re: REGEXP for alphanumeric then using formula for conversion.

Thank you, Tom!