Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

tyember1
New Contributor II

How to extract numbers out of varying text fields

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.

1 Solution

Accepted Solutions

Re: How to extract numbers out of varying text fields

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

Left(FieldName, 8) as LPN

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

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

3 Replies
shraddha_g
Honored Contributor III

Re: How to extract numbers out of varying text fields

Please provide some sample data in excel

Re: How to extract numbers out of varying text fields

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

Left(FieldName, 8) as LPN

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

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

Quy_Nguyen
Contributor III

Re: How to extract numbers out of varying text fields

Hi Tyler,

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

Best.