Remove text without removing decimals

nitot
nitot Member
in Archive

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!

Tagged:

Comments

  • NewsomSolutions
    NewsomSolutions Contributor

    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
    nitot Member

    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
    Ashleigh Coach

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

    **If this answer solved your problem be sure to like it and accept it as a solution!

Categories