Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need to extract an ID from the free text column.
Like I have to extract 71370.01, 71335.01, 71508.01,74093.01 etc.,for the below file.
Attaching the same data for your reference.
Certification - 71370.01 |
BNZ Properties Helpdesk Certification - 71335.01 - Furniture required |
Request Number: 70609.02 |
-71508.01 |
: 71809.01 |
: 71987.01 |
BNZ550557 |
72514.01 - Electrical required |
72717.01 - Electrical required |
TEST |
TEST 2 |
-73422.01 |
WO73969.01 |
: 73748.01 |
74059.01 - Carpentry required |
Request Number: 74271.01 |
Request Number: 74209.01 |
74093.01 - Carpentry required |
74741-01 |
75310-01 |
:74995.01 |
75161.01 - Carpentry required |
Request Number: 76302.01 |
BNZ Properties Helpdesk Certification - 76387.01 - Plumbing required |
if(Len(KeepChar(Field,'0123456789.'))=8,KeepChar(Field,'0123456789.'))
Keepchar() to strip away all of the irrelevant characters and length check to make sure it's still the right length (since e.g. TEST 2 would strip down to just 2, which isn't valid). You could add further if() statements to check e.g. that the sixth character is a period and that the first five and last two are numeric.
if(Len(KeepChar(Field,'0123456789.'))=8,KeepChar(Field,'0123456789.'))
Keepchar() to strip away all of the irrelevant characters and length check to make sure it's still the right length (since e.g. TEST 2 would strip down to just 2, which isn't valid). You could add further if() statements to check e.g. that the sixth character is a period and that the first five and last two are numeric.