Extract date / extract time / strip data
Hi,
Am Wed, 21 Sep 2016 10:26:09 +0100 schrieb Smurfy:
I have the following string of text and I need to extract data for
different columns.
"POS 12/31/15 08:03 8931 PICK N PUMP [location]"
So I need to extract the date for one column, extract the time for
another, and finally strip "POS 12/31/15 08:03 8931" for another. For
the last item where I need to strip, that is a set number of characters
in every row, what follows varies.
your string in A1.
Date:
=INT(--MID(A1,FIND(" ",A1)+1,14))
Time:
=MOD(--MID(A1,FIND(" ",A1)+1,14),1)
Position:
=LEFT(A1,FIND("#",SUBSTITUTE(A1," ","#",4))-1)
Regards
Claus B.
--
Windows10
Office 2016
|