Extracting interest Rate from Text

The attached file shows an example of text on rows. I need to know how to go about extracting the Interest rate from a row that contains a % and place the Interest rate value in a new column. The only consistent thing I can see is the % character comes immediately after the Interest rate Value. However, the number of characters before and after the decimal point varies.

Can someone assist me please.

Thank you

Book12.xlsx (8.8 KB)

See if this will help (all data transformation handled in the Excel file for this sample)

NOTE - I did change the due dates in the original data, so that I didn’t need to include a transformation step to handle the difference between date formats (my native date format is mm/dd/yyyy)Book12.xlsx (103.2 KB)


1 Like


@Heather beat me to it, but here’s my Power Query approach.

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc1BCoJAFADQq3wG2sXM96cOLQupTUVE0EJdiH5kKGdCR6nbp9kBOsDjpak4uLJ4wN4N3NqGrYddbytja9jUbMt3ZimUEeICkp4hiBSGipC0yJepOLdmKDzDtrB3uHLbQMJP1xnfZRalng3FCleTCb7mxt14mBdXcJnskSvTN7M+Oc8jJUn0C2mtUE84/BcHUutoxlphPFkSef4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Text", Splitter.SplitTextByEachDelimiter({"Due"}, QuoteStyle.Csv, true), {"Text.1", "Text.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Text.1", type text}, {"Text.2", type text}}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Changed Type1", "Text.1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Text.1.1", "Text.1.2", "Text.1.3"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Character Transition",{"Text.1.2", "Text.1.3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Merged", "Interest Rate"}, {"Text.1.1", "Funding Agency"}, {"Text.2", "Date"}}),
    #"Added Custom Column" = Table.AddColumn(#"Renamed Columns", "Custom", each let splitDate = Splitter.SplitTextByDelimiter("/", QuoteStyle.None)([Date]), splitsplitDate0 = Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)(splitDate{0}?) in Text.Combine({Text.Reverse(Text.Middle(Text.Reverse([Date]), 5, 1)), Text.Combine(splitsplitDate0, "/"), Text.End([Date], 5)}), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom Column",{"Date"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom", type date}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"Custom", "Date"}})
    #"Renamed Columns1"


Hope this is helpful. Solution file attached. You’ll need to change the type on interest rate, but didn’t know if you wanted it as a percentage w/ 2 dec. places or decimal #.

Thank You Heather and Brian

Could you explain what the Character Transition code does…

It wasn’t in the sample sent earlier but the attached file shows a text that doesnBook12.xlsx (103.2 KB) 't work with Heather’s approach.


Character transition is another way of splitting text – either as it transitions from letter to number or vice versa. In this case once we stripped the date off the right-hand side of the interest rate, we used character transition to separate the funding agency from the interest rate on the left-hand side.

– Brian

Hi @AllisterB,

Just out of curiosity I tried “Column from Examples” (you can find that on the ‘Add Column’ tab on the ribbon). Which I never used before but it picked up both Date and Percentage generating this M code

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    FromExample_Date = Table.AddColumn(#"Changed Type", "Custom", each let splitColumn1 = Splitter.SplitTextByDelimiter("% ", QuoteStyle.None)([Column1]) in Text.Combine({Text.Middle(splitColumn1{1}?, 4), Text.Middle(splitColumn1{0}?, 50, 10)}), type text),
    FromExample_Perc = Table.AddColumn(FromExample_Date, "Text Between Delimiters", each Text.BetweenDelimiters([Column1], "#(lf)", " "), type text)

Because this uses text functions the results are all text values but you can change that of course

Here’s your sample file. eDNA - extract data from text string.xlsx (163.6 KB)
I hope this is helpful

Sorry Allister, I shouldn’t have assumed your data would always have the Due Date included, or that the format of the fields would be the same.