text to Number - try otherwise?

Hi DA_B ,

In general, a column that mix numbers and text in Power Query cannot be defined as numeric types.

If it is easy to extract numbers in your column, you can try the following formula.

=Text.Combine(List.Select(Text.ToList([Column1]), each Text.Contains("0123456789", _)), "")

This formula works by converting the text in the column to a list of characters using the Text.ToList function, and then selecting only the characters that are numbers using the List.Select function and the Text.Contains function. The resulting list of numbers is then combined back into a single text string using the Text.Combine function.

Note that this formula will only extract numbers that are contiguous in the text string. If there are non-numeric characters between the numbers, they will be ignored. If you need to extract numbers that are separated by non-numeric characters, you will need to modify the formula accordingly.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.