Grab a number from a text string

[bnelson111]

Field Name = BillReqText

Below is the text string that changes from the field BillReqText.

I wish to grab the 5 digit number (62467) which also changes however will always be 5 digits and may not start with a 6 however will not start with anything lower than 4.

"PLRF32444EEEEEC - rrr tell REGDD 1L NBC HFSS

Need this NUMBER 62467

2 INVOICED 08/10 - ADD 3 CHARGED 01/12 - TOTAL 5 

PO 17222798/OJDDDW"

Thanks Brett

[Lisa_P]

Try this:

Subfield(TextBetween(BillReqText, 'NUMBER ', 'INVOICED'), ' ', 1)

Which is searching for the text between NUMBER and INVOICED and then only taking the first part before the space.

[MarcoWedel]

Hi,

as a front end expression implementing all your conditions might be impractical, I would suggest extracting these numbers in the script, e.g. like this:

mapNumLet:
Mapping
LOAD Chr(RecNo()), If(IsNum(Chr(RecNo())),'0','_')
AutoGenerate 65535;

tabBillReqText:
LOAD *,
     MapSubString('mapNumLet',BillReqText) as BillReqTextFormat 
Inline [
    BillReqText
    PLRF32444EEEEEC - rrr tell REGDD 1L NBC HFSS
    Need this NUMBER 62467
    Need this NUMBER 52467
    2 INVOICED 08/10 - ADD 3 CHARGED 01/12 - TOTAL 5
    PO 17222798/OJDDDW
    another number 44444 amidst some text
    one number at the end 98765
    Same number in different text 98765
    55555 number at the beginning
    two numbers 56788 in one 56789 text?
    oops 1234 only 4 digits
    12345 is too small
    66666 starts with 6
    no number here
];

tabBillReqTextNum:
LOAD *
Where BillReqNum >= 40000 and not BillReqNum like '6*';
LOAD BillReqText,
     Mid(BillReqText,Index('_'&BillReqTextFormat&'_','_00000_',IterNo()),5) as BillReqNum,
     IterNo() as BillReqNumSeq
Resident tabBillReqText
While IterNo() <= SubStringCount('_'&BillReqTextFormat&'_','_00000_');

hope this helps

Marco