A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Extract date / extract time / strip data



 
 
Thread Tools Display Modes
  #1  
Old September 21st 16, 10:26 AM
Smurfy Smurfy is offline
Junior Member
 
First recorded activity by ExcelBanter: May 2012
Posts: 10
Default Extract date / extract time / strip data

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.


Thanks in advance for any help.

I would also note, after having searched to try and solve this without posting, It might help both me and anyone who sees this in the future to explain the parts of the formulas used. I found several formulas but the example/original data wasn't the same and the parts were not explained so I couldn't figure out how to change the formula provided to suit my needs.
Ads
  #2  
Old September 21st 16, 11:41 AM posted to microsoft.public.excel.worksheet.functions
Claus Busch
external usenet poster
 
Posts: 3,423
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
  #3  
Old September 21st 16, 12:37 PM
Smurfy Smurfy is offline
Junior Member
 
First recorded activity by ExcelBanter: May 2012
Posts: 10
Default

Ok the first two work but I guess I wasn't clear, I need to strip that information out and leave the remaining. The formula you posted results in removing the information I need.
  #4  
Old September 21st 16, 12:54 PM posted to microsoft.public.excel.worksheet.functions
Claus Busch
external usenet poster
 
Posts: 3,423
Default Extract date / extract time / strip data

Hi,

Am Wed, 21 Sep 2016 12:37:17 +0100 schrieb Smurfy:

> Ok the first two work but I guess I wasn't clear, I need to strip that
> information out and leave the remaining. The formula you posted results
> in removing the information I need.


try:
=MID(A1,FIND("#",SUBSTITUTE(A1," ","#",4))+1,99)


Regards
Claus B.
--
Windows10
Office 2016
  #5  
Old September 21st 16, 01:55 PM
Smurfy Smurfy is offline
Junior Member
 
First recorded activity by ExcelBanter: May 2012
Posts: 10
Default

I end up with a blank cell with that one
  #6  
Old September 21st 16, 01:56 PM
Smurfy Smurfy is offline
Junior Member
 
First recorded activity by ExcelBanter: May 2012
Posts: 10
Default

wait sorry it works ... i missed resseting one of the cell references
  #7  
Old September 22nd 16, 02:48 AM
vnfastacc1 vnfastacc1 is offline
Banned
 
First recorded activity by ExcelBanter: Sep 2016
Posts: 2
Default

Lý thuyết v* b*i t*p ôn thi Chứng chỉ đại lý thuế v* h*nh nghề CPA 2016
https://www.youtube.com/watch?v=tBQyLVTCS9A
Link video t*i liệu lý thuyết v* b*i t*p các môn ôn thi chứng chỉ h*nh nghề CPA v* chứng chỉ
đại lý thuế, các bạn theo dõi nhé, tối về rảnh rỗi sẽ up dần trọn bộ t*i liệu các môn lên. Nãm nay mình cũng thi cả 2 chứng chỉ n*y, ai có góp ý gì thì comment dýi video để cùng ôn luyện nhé.
Chúng ta cùng CỐ NHÊÊÊÊNNNN
  #8  
Old September 22nd 16, 04:41 AM
Smurfy Smurfy is offline
Junior Member
 
First recorded activity by ExcelBanter: May 2012
Posts: 10
Default

Ok last one i swear ...

2016 0116 1821 - R.jpg

I need to pull the time out of that string, in this particular case 1821.

TAA
  #9  
Old September 22nd 16, 06:38 AM posted to microsoft.public.excel.worksheet.functions
Claus Busch
external usenet poster
 
Posts: 3,423
Default Extract date / extract time / strip data

Hi,

Am Thu, 22 Sep 2016 04:41:04 +0100 schrieb Smurfy:

> Ok last one i swear ...
>
> 2016 0116 1821 - R.jpg
>
> I need to pull the time out of that string, in this particular case
> 1821.


try:
=MID(A1,FIND("#",SUBSTITUTE(A1," ","#",2))+1,4)

If there are always 10 digits in front you can also try:
=MID(A1,11,4)


Regards
Claus B.
--
Windows10
Office 2016
  #10  
Old September 22nd 16, 07:48 AM
Smurfy Smurfy is offline
Junior Member
 
First recorded activity by ExcelBanter: May 2012
Posts: 10
Default

Quote:
Originally Posted by Claus Busch View Post
Hi,

Am Thu, 22 Sep 2016 04:41:04 +0100 schrieb Smurfy:

> Ok last one i swear ...
>
> 2016 0116 1821 - R.jpg
>
> I need to pull the time out of that string, in this particular case
> 1821.


try:
=MID(A1,FIND("#",SUBSTITUTE(A1," ","#",2))+1,4)

If there are always 10 digits in front you can also try:
=MID(A1,11,4)


Regards
Claus B.
--
Windows10
Office 2016

I end up with 1821 not 18:21
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract time from date K[_2_] Excel Programming 4 August 26th 09 04:49 PM
How to extract time and date Hemanth Excel Discussion (Misc queries) 2 June 27th 07 03:00 PM
extract date between a given time frame B New Users to Excel 1 April 3rd 07 09:18 PM
Extract the hour from a cell showing the date and time Paul Sexton Excel Worksheet Functions 2 February 27th 06 07:15 PM
extract time from excel serial date Dan W[_2_] Excel Programming 4 June 8th 04 06:52 PM


All times are GMT +1. The time now is 06:48 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.