View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Claus Busch Claus Busch is offline
external usenet poster
 
Posts: 3,872
Default 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