Remove text without removing decimals

Reply
Yellow Belt

Remove text without removing decimals

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:

10

854578

 

749687587125516

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

Thanks!

Brown Belt

Re: Remove text without removing decimals

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.

Yellow Belt

Re: Remove text without removing decimals

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.

Major Blue Belt

Re: Remove text without removing decimals

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

Announcements
Looking for the latest Community solutions? Please visit our accepted solutions board here!