Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 21st 16, 10:26 AM
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.

  #2   Report Post  
Old September 21st 16, 11:41 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,536
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   Report Post  
Old September 21st 16, 12:37 PM
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   Report Post  
Old September 21st 16, 12:54 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,536
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   Report Post  
Old September 21st 16, 01:55 PM
Junior Member
 
First recorded activity by ExcelBanter: May 2012
Posts: 10
Default

I end up with a blank cell with that one


  #6   Report Post  
Old September 21st 16, 01:56 PM
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   Report Post  
Old September 22nd 16, 02:48 AM
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   Report Post  
Old September 22nd 16, 04:41 AM
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   Report Post  
Old September 22nd 16, 06:38 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,536
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   Report Post  
Old September 22nd 16, 07:48 AM
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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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:55 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017