Extract number from text field

[#1]

hi in the following excel applikation i wish to extract only the last four numbers off each row.

if a row is lacking the four numbers (companycode) i wish not to read them.

i have tried trim function without luck as each of the rows have different lengths;

any suggestions?

Best,

Brad

[Gysbert_Wassenaar]

I suggest you attach the excel file.

[#3]

Thanks i have attached the excel sheet

[swuehl · Accepted solution]

Try something like

Company:

LOAD

     Company,

     if(isnum(right(Company,4)),right(Company,4)) as Company_num,

FROM

CompanyList.xlsx

(ooxml, embedded labels, table is Sheet1);

or if you only want to read these companies:

Company:

LOAD

     Company,

     right(Company,4) as Company_num

FROM

CompanyList.xlsx

(ooxml, embedded labels, table is Sheet1)

where isnum(right(Company,4));