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.

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.

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

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

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

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

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