# Power Query - Extract last number from text



## pepe74287 (Mar 16, 2016)

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 (Mar 16, 2016)

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 (Mar 18, 2016)

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 (Mar 18, 2016)

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 (Mar 18, 2016)

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 (Mar 18, 2016)

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 (Mar 21, 2016)

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 (Mar 21, 2016)

Useful said:


> Helo,
> 
> another solution
> 
> ...



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


----------

