Extract numbers from one line field

[has75042]

From the below field, how do I extract numbers

for example:

004583 

0002195 

001

20040921

 I am using the trim left and mid function this is the right function to use?

Thanks in advance. 

[jwjackso]

If the fields are fixed width, that may be the easiest method.  It looks like most of the fields are space delimited, you might want to look at the subfield function

[Vegar · Accepted solution]

Try this

LOAD

subfield ([Your numbersfield], ' ') as [Extracted number],

...

FROM source;

[has75042]

subfield function seems to be working. 

SubField(ABNOTE, ' ', -1) is giving me this # 0200709190660, how do I convert this number into date.

[sasiparupudi1]

SubField('004583 0002195 001 20040921',' ',1)

SubField('004583 0002195 001 20040921',' ',2)

SubField('004583 0002195 001 20040921',' ',3)

SubField('004583 0002195 001 20040921',' ',4)

[has75042]

Hey,

How can I convert this #  0201010310600 into date.

I have tired make date function, floor. None of them seems to be working. Please let me know a function that I can use to convert above number into date

Thanks in advance.  

[jwjackso]

How does the number 0201010310600  represent a date?  Is it Oct 31 2019 at 6 am?  Why does it have a leading zero.  Is it the number of milliseconds, seconds, minutes from a date?

[has75042]

Yes, it is Oct 31 2019 at 6 am. The leading zero doesn't represent anything. I need to remove that leading zero

[jwjackso]

The MakeDate function worked.  I would have expected the Date# function to work.

=MakeDate(Num#(Mid(DT,2,4)),Num#(Mid(DT,6,2)),Num#(Mid(DT,8,2)))

[sasiparupudi1]

Try Timestamp

=Timestamp(Timestamp#(Num(A),'YYYYMMDDhhmm'))