How to extract codes & names from a long list of data array with a different lengths

[nevilledhamsiri]

Hi,

Given below is a sample  of data loaded under CREDITOR_CODE which are having varying length as I have counted in excel. I have shown only a part of data & the list contains more than 100 records with different length in each row. Can our expert propose an expression to extract codes & names separately?

Thanks neville

CREDITOR _CODE	LEN
CREDITOR CODE  127524 M.P.PATHIRANA	37
CREDITOR 127454  S.SIVAKUMARA	30
CREDITOR CODE 52514-011 SEYLAN BANK	35
CREDITOR CODE 145214_125  COMMERCAL BANK	40
CREDITOR 1452142  COMMERCCAI CREDIT	36
CREDITOR 124114-011 PAN ASIA BANK	34
I need to extract all numbers given in each row as creditor codes
I need to extract the creditor names which are on the right side of each row

[Clever_Anjos]

Please try this

Base:

LOAD * INLINE [

    CREDITOR_CODE, LEN,

    CREDITOR CODE  127524 M.P.PATHIRANA, 37,

    CREDITOR 127454  S.SIVAKUMARA, 30,

    CREDITOR CODE 52514-011 SEYLAN BANK, 35,

    CREDITOR CODE 145214_125  COMMERCAL BANK, 40,

    CREDITOR 1452142  COMMERCCAI CREDIT, 36,

    CREDITOR 124114-011 PAN ASIA BANK, 34

];

load

CREDITOR_CODE as FULL_LINE,

[creditor codes],

Trim(Mid(CREDITOR_CODE,Index(CREDITOR_CODE,[creditor codes])+Len([creditor codes]))) as [creditor names];

load

CREDITOR_CODE,

KeepChar(CREDITOR_CODE,'0123456789-_') as [creditor codes]

Resident Base;

Drop Table Base;

[sunny_talwar · Accepted solution]

Another option

Base:

LOAD *,

Mid(CREDITOR_CODE, FindOneOf(CREDITOR_CODE, '0123456789')) as [creditor names];

LOAD * INLINE [

    CREDITOR_CODE, LEN,

    CREDITOR CODE  127524 M.P.PATHIRANA, 37,

    CREDITOR 127454  S.SIVAKUMARA, 30,

    CREDITOR CODE 52514-011 SEYLAN BANK, 35,

    CREDITOR CODE 145214_125  COMMERCAL BANK, 40,

    CREDITOR 1452142  COMMERCCAI CREDIT, 36,

    CREDITOR 124114-011 PAN ASIA BANK, 34

];