How to extract the middle number in a string

[Anonymous]

Hello,

I'm trying to extract the number of a materiel within a string.

The problem is that users tend to add additional information into the input field (web page), so that when I load the the data with qlik, the field has more information then I want.

Lets say I have a string like this:

let vExampleString1 = 'This is my number #1234567890';

let vExampleString2 = 'Package number #42Part 1';

I would like to extract the data to be:

'1234567890'

'42'   // important only the 42 and not the 1 from 'Part 1'

A few rules:

1) the number in Question always starts with a #

2) the number in question ends with the fist non number

I have tried to accomplish it with:

     Mid('$(vExampleString2)' , Index('$(vExampleString2)', '#'), 10)

     KeepChar('$(vExampleString2)','0123456789')

but wasn't able to get it done. Can someone help me?

[sunny_talwar · Accepted solution]

May be this

Table:

LOAD *,

Left(Mid(String&'a', Index(String&'a', '#')+1), FindOneOf(Lower(Mid(String&'a', Index(String&'a', '#')+1)), 'abcdefghijklmnopqrstuvwxyz~!@#$%^&*()_+{}:"<>?/.,'';][=-`')-1) as Number;

LOAD * INLINE [

    String

    This is my number #1234567890

    Package number #42Part 1

];

[Quy_Nguyen]

It's so nice solution by adding 'a' at the end of String to make FindOneOf function work in any case

[sunny_talwar]

Hahahaha yes