Search for text in a string

[prees959]

Hi,

I have a lengthy string that I need to search for certain values and extract them.  

For example :

a field called test results :

"ID=9879867234, Confidence=90, Desc=Non destructive testing part2 (KPP=081678402  KPP-8) UNIT Area: NORTH EAST SECTOR (077)"

"ID=1584858834, Confidence=80, Desc=Dye Examination (KPP=998478111  KPP-9) UNIT Area:  AAB (5)"

Ideally I need to create some fields from these strings. In the above examples :

Confidence : 90

KPP : 081678402 

UNIT Area : NORTH EAST SECTOR (077)

Confidence : 80

KPP : 998478111 

UNIT Area : AAB (5)

Can anyone help please?

Many thanks,

Phil

[datanibbler]

Hi,

use the INDEX() function to find where the nth (probably 1st?) instance of your searched-for piece begins within that string (better use a TRIM() around it all to avoid leading and trailing spaces) and then use the MID() and LEN() functions to cut out just the piece you want.

Best regards,

DataNibbler

[sasiparupudi1 · Accepted solution]

Try

Load

x,

TextBetween(x,'Confidence=',',') As Confidence,

Trim(TextBetween(x,'KPP=','KPP-')) As KPP,

Trim(SubField(x,':',-1)) As [UNIT Area]

Inline

[

x

ID=9879867234, Confidence=90, Desc=Non destructive testing part2 (KPP=081678402  KPP-8) UNIT Area: NORTH EAST SECTOR (077)

ID=1584858834, Confidence=80, Desc=Dye Examination (KPP=998478111  KPP-9) UNIT Area:  AAB (5)

](delimiter is '|');

[prees959]

Perfect - Thank you!