Using an ETL, is it possible to remove text without removing decimals?
For example, I have a dataset that includes values like this:
Using "Text Formatting" to "Only show numbers" results in the following output:
How can I extract the numbers from a column while still keeping the decimals in the correct place?
Is there any other critiera in the data that is consistent? What I mean is that are there fields like "1.0 blah" or it is only "1.0", "9.9999", or "N/A" type examples? If the only other non-number you have to remove is "N/A" could you use "Replace Text". If there are others like "1.0 blah" and there is always a space seperating them you could again use Replace Text again, but time in that second box for search critieria use "RegEx" and create a regex that would remove everything on and after a space. This is a good site to test regex commands https://regex101.com/ . Hope that helps.
Thanks for your help! Unfortunately there is no consistency to the nonstandard data, so I can't look for a number of characters, or a certain string.
@nitot you could use RegEx to replace any text that contains letters from the alphabet with some sort of flag and then you could filter out the flag. If you have any cases that contain both letters and numbers then that solution may not work.