Power Query - Extract numbers from Text and insert delimiter

[samye228]

Hello,

I have a free form field that has dates and currency. Sometimes the dates are mm/dd/yy or month name year, i.e., April 2024. The currency does not have decimals but may or may not have a dollar sign $.

I would like to extract the numbers and add a delimiter between them. For example, the following line:

"The FRC was updated on 4/14/2006.Verified via Dan Daily that this building exists. Note this chapel/six post is where the old library used to be and now the library is part of another building.Old FRC was 964,600"

I found where I could use the Text.Select and Table.AddColumn in another post to produce this result: 

4142006964600

What I would like is to have a delimeter between the date 4142006 and the dollar amount 964,600. Ideally, I would like to keep the date format and the currency format and have a delimeter between the two such as: 4/14/2006#964,600.

Thank you.

[dufoq3 · Accepted solution]

Hi @samye228, this could help you for such 2 cases, but you will probably need some update for other situations.

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1Y/BCsJADER/ZfAsbZVS8CiKRwURL+IhdaMNLLvL7tbq3xsVvfkBHpLDvEmGORxGu46x2i4wUEIfDGU28A51OanLaVU1xZ6jnEXVqxCW5HTE3pE7yrokoe3FGnEX8E1STgXWPvMbnToKbMskNwSfMlQaOo5PyvDWwEobKd7RJw3IHi2DnIHzw8vywXoXKGb4s2KvJH5Ti42++RSYNfW4qarRcfyz2DxEsf9d7vgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Ad_Extracted = Table.AddColumn(Source, "Extracted", each 
        [ a = {"0".."9", ",", "/"},
          b = Text.SplitAny([Column1], " ."),
          c = List.Transform(b, (x)=> 
                if (try Number.From(x, "en-US") otherwise false) is number then x else
                if (try Date.FromText(x, [Format="M/d/yyyy", Culture="en-US"]) otherwise false) is date then x else
                if (try Date.FromText(x, [Format="MMMM/d/yyyy", Culture="en-US"]) otherwise false) is date then Date.ToText(Date.FromText(x, [Format="MMMM/d/yyyy", Culture="en-US"]), [Format="M/d/yyyy", Culture="en-US"]) else false ),
          d = List.Select(c, (x)=> x <> false),
          e = Text.Combine(d, "#")
        ][e], type text)
in
    Ad_Extracted

[PhilipTreacy]

@samye228 

Impossible(?) to do this as it's free form data entry. 

How do we know if the numbers entered are a date or currency?

How do we know which order those values appear in the data?

How do we know if there are more than one occurence of each type of data?

Sorry, with free form entry this type of problem isn't doable.  The only way you might do it would be if the date is always the same format (same delimiters, always use 2 digits for month and day and 4 digits for year).  You could then be able to distinguish dates from currency.  But as I said, if there is more than one date and/or more than one curency values, it gets very complicated if not impossible.

You'd be better off redesigning the data entry to control the way the data is entered.

Phil