4 Replies Latest reply on Feb 17, 2016 8:14 AM by Simon Runc

# Calculated field to turn this string into number?

Hi, I would like to turn a field (currently data type = string) that looks like this:

#### 150,000 lbs. (68kg)

Into a number:

#### 150000

So I would like to get rid of everything after the first number

Could someone advise on how to do this?

Also, I would be interested to see a tutorial on how to write formulas for Tableau calculated fields... haven't been able to find one yet

Thanks

• ###### 1. Re: Calculated field to turn this string into number?

Good work

• ###### 2. Re: Calculated field to turn this string into number?

Hi D C

What you need to achieve is easily done using Datawatch Monarch, which is a tool for data preparation and data blending. You can download a free version here:

http://www.datawatch.com/monarch-free-offer/

When you are done data prepping, you can export the data to a .TDE file, or csv, or Excel etc.

I have inserted a short video that shows how it is done.

Best regards, Theo Klemming, product expert, Datawatch Corporation

Datawatch is an official partner of Tableau Software

• ###### 3. Re: Calculated field to turn this string into number?

An easier and free method would be to use a Regular Expression:

2 of 2 people found this helpful
• ###### 4. Re: Calculated field to turn this string into number?

hi DC,

for some intro (and then more advanced videos  on Calculated field, the On-Demand Training is a great starting point)....Tableau Training and Tutorials

for your question, you can  use the String manipulation functions to do this (I've assumed that all your values are in this form with lbs). The formula would be

INT(LEFT([Weight],FIND([Weight],'lbs')-2))

This works similar to Excel. The FIND function returns the position in the String of where it finds 'lbs' and takes all the values to the left of this (-2 for the leading space after the value), and then casts to an Integer.

Hope this helps and makes sense, but if not please post back.