Remove text without removing decimals

nitot

Using an ETL, is it possible to remove text without removing decimals?

For example, I have a dataset that includes values like this:

1.0

8.54578

N/A

7.49687587125516

.....

Using "Text Formatting" to "Only show numbers" results in the following output:

854578

749687587125516

How can I extract the numbers from a column while still keeping the decimals in the correct place?

Thanks!

Find more posts tagged with

Magic ETL

Comments

NewsomSolutions

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.

nitot

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.

Ashleigh

@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.