Power Query - Extract last number from text

[pepe74287]

Hello,

I'm trying to extract last number from text (see below):

Dataset

INFX1
INFX2
INFX20
INFX1430

Desired output:
1
2
20
1430

Any help is appreciated.

Thanks

Pepe

 

[anvg]

Hi

let
    extractNumber = (this as text) as text =>
    let
        digits = {"0","1","2","3","4","5","6","7","8","9"},
        pos = Text.PositionOfAny(this,digits,Occurrence.First),
        len = Text.Length(this)
    in
        Text.Range(this,pos,len-pos)
in
    extractNumber("INF102245")

Regards,

 

[pepe74287]

Hi,

Apologise for late reply.

Thank you for provided code, I was trying to include your code into my query, but discovered 2 problems:
1. If I put extractNumber("4e123")" , I get 4e123 (would need to get only the last number - 123)
2. Can I use column name as an input for extractNumber? The column I need to run this function on is called "Body", type text
I've tried this: extractNumber({"Body"}), but got an error: We cannot convert a value of type List to type Text.

Thanks a lot

Pepe

 

[anvg]

Hi
1. Could you write all possible combinations? If I understand you right it is needed to get last digits from a text string which always contains digits in its own end.
In this case you could try such code

let
    extractNumber = (this as text) as any=>
    let
        chars = {"a".."z"},
        pos = Text.PositionOfAny(Text.Lower(this),chars,Occurrence.Last) + 1,
        len = Text.Length(this)
    in
        Text.Range(this,pos,len-pos)
in
    extractNumber("IN98F102245")

2. Yes, if a column name is reference to a field of record (simpler it is single value).
Regards,

 

[pepe74287]

Hi,
1. Text consists only from letters A...Z and digits. And I only want to return last number in a string. And yes, there's always at least one digit at the end.

2. The code seems to be working exactly as expected with your given example, but I'm not sure what syntax to use when replacing "IN98F102245" with column name (in my case the column name is "Body").

Pepe

 

[anvg]

Alright
It is an example with using of column name

let
    extractNumber = (this as text) as any=>
    let
        chars = {"a".."z"},
        pos = Text.PositionOfAny(Text.Lower(this),chars,Occurrence.Last) + 1,
        len = Text.Length(this)
    in
        Text.Range(this,pos,len-pos),
    demoTable = Table.FromColumns({ {"IFT12345","I12FT345","IF123T45"} },{"TextAndNumbers"}),
    lastNumbers = Table.AddColumn(demoTable,"lastNumber", each extractNumber([TextAndNumbers]))
in
    lastNumbers

Regards,

 

[Useful]

Helo,

another solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Result = Table.AddColumn(Source, "Custom", each Text.End([[COLOR=#333333][FONT=Verdana]Body[/FONT][/COLOR]],Text.Length([[COLOR=#333333][FONT=Verdana]Body[/FONT][/COLOR]])-Text.PositionOfAny([[COLOR=#333333][FONT=Verdana]Body[/FONT][/COLOR]],{"a".."z","A".."Z"},Occurrence.Last)-1))
in
    Result

 

[pepe74287]

Helo,

another solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Result = Table.AddColumn(Source, "Custom", each Text.End([[COLOR=#333333][FONT=Verdana]Body[/FONT][/COLOR]],Text.Length([[COLOR=#333333][FONT=Verdana]Body[/FONT][/COLOR]])-Text.PositionOfAny([[COLOR=#333333][FONT=Verdana]Body[/FONT][/COLOR]],{"a".."z","A".."Z"},Occurrence.Last)-1))
in
    Result

Thank you, I was able to include your code into mine and it's working as expected . I'm not that skilled in PQ code language so I was struggling to understand anvg solution (although I'm sure it works as well).

Pepe