Data transformation- Specific cell

Question

Hi,

I am currently working with a data file that includes a column defined as a string type. However, some cells within this column contain numeric values that I would like to use for calculation purposes. Is it possible in Alteryx to convert specific cells—within a string-type column—to a numeric data type such as double?

I have attempted several formula-based approaches, but unfortunately, they did not yield the desired outcome to convert these cells to store double (number) values. Any guidance or suggestions on how to achieve this would be greatly appreciated

HannahBrauer223 · Answer

I made some sample data showing text and numbers and performed some transformations on it to get all the numbers into its own number column and all the text in another column. This would allow you to use the numbers for calculations. Here's a summary of what I did:

1. Assign RecordID

2. Confirm my Test column is starting as a Vstring

3. Wrote a formula to determine if the value in Test is a true string or actually a number.

4. Used CrossTab to separate the numbers and text.

5. Joined back to original dataset using RecordID.

6. Cleaned up new number column

The result is 2 new columns, 1 contains all numbers from the original Test column and the other contains all text, while still being tied to the correct rows.

--->

Thanks,

Hannah Brauer

Crowe LLP

Column with Text and Numbers.yxzp

dreldrel · Answer

You may want to use Regex to extract your values first and then do the calculation you need. Once you get the results, you can add them back to the original places. If this helps, feel free to like the comment

cjaneczko · Answer

You can use tonumber([String Field]) to convert all of the ones with numbers in the rows to number and do whatever math you need to. Any of the ones without numbers wont be impacted.