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.

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

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.

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

I end up with a blank cell with that one

wait sorry it works ... i missed resseting one of the cell references

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

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

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

I end up with 1821 not 18:21

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

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

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.

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

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