Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
Within a field in the script, I'm trying extract a small section that includes a client ID (Underlined in the below example). As you can see, the number of leading zeros can differ as client ID's range from 4 to 9 digits, and never start with 0. After the client ID ends in the string, '-' appears. '-' at times appears for a second time in some of the records that have an account name that includes '-'. I've tried a number of combinations of left/index combinations but cannot quite get there, help please! I was close when indexing as such:
left(REPLACE(LTRIM(REPLACE(Description,'0',' ')),' ','0'),Index(Description,'-',-1)-Index(Description,'-',+1))
Data:
0000544056-01 Gross Wages Cost
0000544056-02 FICA ER Cost
0000544056-3
0000544056-03 Medicare ER Cost
0000544056-04 FUTA ER Cost
0000544056-5
0000544056-33 401k Payable - S
0000544056-33 Medcom Flex Paya
0000001655-30 SUTA Payable
0000001655-32 W/C - Zurich Pol
0000001655-33
0000001655-33 401k Payable - S
0000001655-33 Garnishments Pay
0000001655-33 Other Health Ins
0000001655-34
0000001655-34 401k Payable - S
0000001655-34 Florida Blue Hea
0000001655-51 Accounts Receiva
0000001655-54 Gross Wages Reve
0000001655-55 FICA Revenue
How about this
Num(Num#(SubField(FieldName, '-', 1)))
Thanks for the accurate and prompt reply Sunny, much appreciated!!!