How to extract numbers out of varying text fields

[tyember1]

Hello Qlik Community,

I am struggling to find a way to pull out numbers from text fields. I will start off with two examples of our text fields.

First:

"BME sample of ___ collected @1000, had a micro result out of spec. Placing affected time frame between good checks @0800-1200 on Cat 1 hold for further review.

LPNs

31571058

31571094

31571098

31571105

31571111

31571143"

Second:

"R9B1 had a safety shutdown during the come-up fill due to an open door during process, which lead to an uncleared deviation. It was then restarted as R9B2. Unsure of the root cause, operators believe it to be a false reading, possibly due to a sensor issue. CAT1 Hold for further review.

LPNs and quantity will be updated.

UPDATE - TM

LPNs

31571149                      190513          QC      TR0900        

31571242                      190513          QC      TR0900        

31571244                      190513          QC      AB06FLOOR"

The issue is I need to separate out the LPNs from the rest of the text and put them into their own field per 8 digit LPN. We are debating creating a new table and querying to populate the table creating a record for each LPN. Then importing that table. All LPNs start with the number 3 but it can vary after that.

[shraddha_g]

Please provide some sample data in excel

[rubenmarin · Accepted solution]

Hi Tyler, if the LPN is always the first 8 characters you can use left:

Left(FieldName, 😎 as LPN

Or you can take the data until the first empty space and keep only numbers:

KeepChar(Subfield(FieldName, ' ', 1), '0123456789')

[Quy_Nguyen]

Hi Tyler,

Please take a look at the attached files. Is that what you looking for?

Best.