Forum Discussion
Pulling numeric-only values from a alphanumeric field
Yours is the simplest solution which can be done with just the interface. However, if I were to do a custom column, it would be
// Attempts to convert the value in [column] to a number and returns null if it fails try Number.From([column]) otherwise null
EH_MW , There are a few options discussed here, see if those can help
https://goodly.co.in/separate-numbers-from-text-excel-powerquery/
These videos deal with splitting the value. I'm looking to create a new column that completely ignores the values that have an alpha value in them. I need to do this because there are values such as "40M" instead of "40,000,000". If I simply split off the the numers and letters, I would be left with 40. Our users would prefer to ignore these values altogether until the data is cleaned up.
Instead, I'm looking for a way to create a new column that uses the monetary value if it exists (numbers only) and, if alpha characters exist in the value, set those values to 0 or ignore them completely.
EH_MW ,
-
Replace every “,” with an empty string, and remove all spaces (i.e., replace them with a zero‑length string).
-
Split the result into two columns at the first transition from a digit to a non‑digit.
-
Then, split the second column at the transition from a non‑digit back to a digit.
-
If the first column is not blank and the second column contains suffixes like “M” or “k,” apply your specific rule for those cases.
-
Define similar rules for any other suffixes or scenarios.
Provide me sample very similar to your sample I can put some code around it
-