Using TONUMBER() to parse out the first number in a concatenated string field

by Luke Donovan

I ran into a bit of an issue on a Friday project with an API I was using which automatically concatenated multiple columns. I was using a variety of data from this season of the Premier League which had goal difference, goal scored, points among other data you’d expect to see. However, the API automatically concatenated these values in one column representing the overall figure, home figure and away figure. Here is what I was playing with:

Figure 1 – API Output

I was only interested in the overall number in each of the columns (11 in total). There are a number of ways you can go about this problem (the beauty of data) e.g. multi-field REGEX_Replace, however Jonathan in DS11 showed me that you can use TONUMBER() and here’s how it works.

Figure 2
New column

  1. Get formula tool and drag onto the canvas
  2. Set up the formula tool using the following syntax: TONUMBER([Desired Field])
  3. Run your workflow and Alteryx has now only returned the first number (See Figure 2)
  4. Duplicate in the same formula tool for any other desired columns

It’s a very easy and simple trick that could save you a messy multi field tool and some messy REGEX. Just be aware that this method does not parse out the OTHER values, just the FIRST value in your concatenated field. If you still wanted all those values then use the REGEX_Replace in a multi field formula tool, but this method is a great way of getting that first number!

Hope you found it useful, if you used it let me know @Luke_Donovan03 😊

Thu 10 Sep 2020

Thu 10 Sep 2020

Wed 19 Aug 2020

© 2022 The Information Lab Ltd. All rights reserved.