Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
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  
Posted to microsoft.public.excel.worksheet.functions
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
  #3   Report Post  
Junior Member
 
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  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
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  
Junior Member
 
Posts: 10
Default

I end up with a blank cell with that one


  #6   Report Post  
Junior Member
 
Posts: 10
Default

wait sorry it works ... i missed resseting one of the cell references
  #7   Report Post  
Banned
 
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  
Junior Member
 
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  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
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  
Junior Member
 
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


  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Extract date / extract time / strip data

Hi,

Am Thu, 22 Sep 2016 07:48:03 +0100 schrieb Smurfy:

I end up with 1821 not 18:21


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


Regards
Claus B.
--
Windows10
Office 2016
  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Extract date / extract time / strip data

Hi again,

Am Thu, 22 Sep 2016 08:58:50 +0200 schrieb Claus Busch:

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


or:
=TIMEVALUE(TEXT(MID(A1,FIND("#",SUBSTITUTE(A1," ","#",2))+1,4),"00"":""00"))


Regards
Claus B.
--
Windows10
Office 2016
  #13   Report Post  
Junior Member
 
Posts: 1
Unhappy

I am trying to make this work with my data and have failed miserably.

The following is in cell C6

03:06 PM @ 05/30/14-06-2014

I need to extract the time (all 8 digits unless - although extracting in 24 hour clock would be perfect) into one column and the 05/30/14 date format into another. From the first "-" marker on is not required.
  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Extract date / extract time / strip data

Hi,

Am Tue, 8 Nov 2016 10:35:57 +0000 schrieb HitchinDragon:

The following is in cell C6

03:06 PM @ 05/30/14-06-2014

I need to extract the time (all 8 digits unless - although extracting in
24 hour clock would be perfect) into one column and the 05/30/14 date
format into another. From the first "-" marker on is not required.


for the time try:
=TIMEVALUE(LEFT(C6,9))
and format the formula cell as expected.

For the date try:
=--MID(C6,FIND("@",C6)+2,8)


Regards
Claus B.
--
Windows10
Office 2016
  #15   Report Post  
Junior Member
 
Posts: 10
Default

Nguyen nay Đang xong vợ cả :v. chồng chị phá em kêu Mới Tâm Thu chỗ Bui Gấu ăn gặp Huyen Gam Jessica Bùi đ*p đấy. về Gọi đi Nguyen trưa gọi M*p
Bảo Hiểm Sức Khỏe
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 12:49 PM.

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

About Us

"It's about Microsoft Excel"