separating text into columns

L

Leiftinspace

New Member
Joined
Sep 29, 2017
Messages
5
So I have a large amount of text that is laid out into the following format:

DATE & TIME | TRANSACTION |BOX|KEY#|KEY NAME | BY USER

9/1/2017 04:04|Key Removal 1 0001 Coordinator Key (NAME)
9/1/2017 07:11|Key Removal 1 0004 Edith Bowen Set (NAME)
9/1/2017 07:40|Key Return 1 0007 HSRC Set 1 (NAME)
9/1/2017 09:53|Key Removal 1 0003 Edith Bowen Set (NAME)

This is a very small sample of the information that I was given. There are MANY more rows than this. I was just given all of this text in an email, but would like to format it into Excel in order to sort the data a bit more easily. However, If I copy and paste it into excel, it just pastes all of it into one cell. My question is, is there a way to simply divide the information into individual cells without having to do a ton of copying and pasting?
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
easy to split off date and time, then key removal / return. what is left probably I would search for key or set, but set 1 is harder. could you sort most and manually tweak the few difficult ones ?

Could the sender help - they copied it from a spreadsheet or database - or maybe they typed it in

what is the symbol after 4 in first row (or did you type it in to help us ?)
 
Upvote 0
That is going to take a program to 'parse' each line and then allocate each part to its respective cell.

Leiftinspace said:
DATE & TIME | TRANSACTION |BOX|KEY#|KEY NAME | BY USER

9/1/2017 04:04|Key Removal 1 0001 Coordinator Key (NAME)
9/1/2017 07:11|Key Removal 1 0004 Edith Bowen Set (NAME)
9/1/2017 07:40|Key Return 1 0007 HSRC Set 1 (NAME)
9/1/2017 09:53|Key Removal 1 0003 Edith Bowen Set (NAME)
Click to expand...
I assume that the "|" were part of the email.
Do "Key Removal" and "Key Return" belong under "TRANSACTION", BOX=1, KEY# is the next 4 digits, BY USER is (NAME) and whatever I skipped is the KEY NAME?

If that is the case one would need to know more, not us, but the program. By that I mean, the BY USER will clearly appear in your data with spaces such that, in your first line of data KEY NAME and BY USER might look like "Coordinator Key Sarah Jones Smith". I assume that you have a list of all items which might form KEY NAME. Maybe you have a list of all BY USER too.

To use your data as a text file would be fine if there was a consistent delimiter, like a "|", comma or space.

Given some answers to my opening question may provide someone with a more coherent answer.
Thanks for your ask.
 
Upvote 0
BrianJN1 said:
That is going to take a program to 'parse' each line and then allocate each part to its respective cell.

I assume that the "|" were part of the email.
Do "Key Removal" and "Key Return" belong under "TRANSACTION", BOX=1, KEY# is the next 4 digits, BY USER is (NAME) and whatever I skipped is the KEY NAME?

If that is the case one would need to know more, not us, but the program. By that I mean, the BY USER will clearly appear in your data with spaces such that, in your first line of data KEY NAME and BY USER might look like "Coordinator Key Sarah Jones Smith". I assume that you have a list of all items which might form KEY NAME. Maybe you have a list of all BY USER too.

To use your data as a text file would be fine if there was a consistent delimiter, like a "|", comma or space.

Given some answers to my opening question may provide someone with a more coherent answer.
Thanks for your ask.
Click to expand...

Yes, the line after the time stamp is part of the original email that I received. "Key Removal" and "Key Return" belong under "Transaction," BOX=1, the next 4 digits are Key #, the next group of words (Coordinator Key, Edith Bowen Set, etc) go under "Key Name," and then the individuals who checked out the keys in question are under "By User." I just erased their names and substituted them for (Name). Hopefully that makes sense. Any additional information would be most appreciated.
 
Upvote 0
Welcome to the MrExcel board!

Yet more questions.
1. Are the names at the end actually in parentheses in your raw data?
2. If not, are they all, say, one-word names, or two-word names?
3. If not, how do you envisage we decide where the KEY NAME ends and the BY USER starts, unless it is by the provision of the list as requested in post #5 , or the provision of a list of all possible "BY USER" names?
4. Also, just to clarify, you said it all pastes into one cell. Did you really mean all in one cell, or did you mean the data in each row pastes into one cell in that row?
 
Upvote 0
Peter_SSs said:
Welcome to the MrExcel board!

Yet more questions.
1. Are the names at the end actually in parentheses in your raw data?
2. If not, are they all, say, one-word names, or two-word names?
3. If not, how do you envisage we decide where the KEY NAME ends and the BY USER starts, unless it is by the provision of the list as requested in post #5 , or the provision of a list of all possible "BY USER" names?
4. Also, just to clarify, you said it all pastes into one cell. Did you really mean all in one cell, or did you mean the data in each row pastes into one cell in that row?
Click to expand...

So, I looked at the info I had, and decided there was nothing too personal here, so I'm just pasting it all here for you to look at and figure out. :) Hopefully this answers questions 1-3. as for #4 , I really meant all in one​ cell.
 
Upvote 0
[FONT=&quot]--------------------------------------------------------------------------------------[/FONT]
[FONT=&quot] DATE &TIME | TRANSACTION |BOX|KEY#|KEY NAME | BYUSER [/FONT]
[FONT=&quot]--------------------------------------------------------------------------------------[/FONT]
[FONT=&quot] 9/1/2017 04:03|KeyRemoval 1 0007 HSRC Set 1 Burk, Michael [/FONT]
[FONT=&quot] 9/1/2017 04:04|KeyRemoval 1 0006 Early Ed Set 2 Nyman, Leif, Leif[/FONT]
[FONT=&quot] 9/1/2017 04:04|Key Removal 1 0001 Coordinator KeyNyman, Leif, Leif[/FONT]
[FONT=&quot] 9/1/2017 07:11|KeyRemoval 1 0004 Edith Bowen SetMartin, Vicki [/FONT]
[FONT=&quot] 9/1/2017 07:40|KeyReturn 1 0007 HSRC Set 1 Burk, Michael [/FONT]
[FONT=&quot] 9/1/2017 09:53|KeyRemoval 1 0003 Edith BowenSet Essman, Layna [/FONT]
[FONT=&quot] 9/1/2017 10:23|KeyReturn 1 0001 Coordinator KeyNyman, Leif, Leif[/FONT]
[FONT=&quot] 9/1/2017 10:23|KeyReturn 1 0006 Early Ed Set 2 Nyman, Leif, Leif[/FONT]
[FONT=&quot] 9/1/2017 13:32|KeyReturn 1 0004 Edith Bowen SetMartin, Vicki [/FONT]
[FONT=&quot] 9/1/2017 13:55|KeyReturn 1 0003 Edith BowenSet Essman, Layna [/FONT]
[FONT=&quot] 9/4/2017 15:40|KeyRemoval 1 0006 Early Ed Set 2 Essman, Ariana, A[/FONT]
[FONT=&quot] 9/4/2017 16:01|KeyRemoval 1 0002 Team Lead Key Kimball, Dan [/FONT]
[FONT=&quot] 9/4/2017 18:38|KeyRemoval 1 0004 Edith Bowen SetEliason, Joseph, [/FONT]
[FONT=&quot] 9/4/2017 18:53|KeyRemoval 1 0013 1037 FM Master Kimball, Dan [/FONT]
[FONT=&quot] 9/4/2017 18:58|KeyReturn 1 0006 Early Ed Set 2 Essman, Ariana, A[/FONT]
[FONT=&quot] 9/4/2017 22:19|KeyReturn 1 0013 1037 FM Master Kimball, Dan [/FONT]
[FONT=&quot] 9/4/2017 23:16|KeyReturn 1 0004 Edith Bowen SetEliason, Joseph, [/FONT]
[FONT=&quot] 9/4/2017 23:49|KeyReturn 1 0002 Team Lead Key Kimball, Dan [/FONT]
[FONT=&quot] 9/5/2017 04:02|KeyRemoval 1 0010 Education Set 2Beutler, Tammy, T[/FONT]
[FONT=&quot] 9/5/2017 04:02|KeyRemoval 1 0005 Early Ed Set 1 Harrison, Jakend,[/FONT]
[FONT=&quot] 9/5/2017 04:03|KeyRemoval 1 0001 Coordinator KeyNyman, Leif, Leif[/FONT]
[FONT=&quot] 9/5/2017 06:42|KeyReturn 1 0010 Education Set 2Beutler, Tammy, T[/FONT]
[FONT=&quot] 9/5/2017 07:05|KeyRemoval 1 0004 Edith Bowen SetMartin, Vicki [/FONT]
[FONT=&quot] 9/5/2017 08:00|KeyReturn 1 0005 Early Ed Set 1 Harrison, Jakend,[/FONT]
[FONT=&quot] 9/5/2017 09:53|KeyRemoval 1 0003 Edith BowenSet Essman, Layna [/FONT]
[FONT=&quot] 9/5/2017 11:49|Key Return 1 0001 Coordinator KeyNyman, Leif, Leif[/FONT]
[FONT=&quot] 9/5/2017 13:58|KeyReturn 1 0004 Edith Bowen SetMartin, Vicki [/FONT]
[FONT=&quot] 9/5/2017 14:50|KeyReturn 1 0003 Edith BowenSet Essman, Layna [/FONT]
[FONT=&quot] 9/5/2017 15:19|KeyRemoval 1 0002 Team Lead Key Kimball, Dan [/FONT]
[FONT=&quot] 9/5/2017 18:38|KeyRemoval 1 0006 Early Ed Set 2 Essman, Ariana, A[/FONT]
[FONT=&quot] 9/5/2017 18:53|KeyRemoval 1 0005 Early Ed Set 1 Nel, Jared, Jared[/FONT]
[FONT=&quot] 9/5/2017 18:59|KeyRemoval 1 0010 Education Set 2Lauchnor, Robert,[/FONT]
[FONT=&quot] 9/5/2017 19:00|KeyRemoval 1 0013 1037 FM Master Kimball, Dan [/FONT]
[FONT=&quot] 9/5/2017 22:42|KeyReturn 1 0006 Early Ed Set 2 Essman, Ariana, A[/FONT]
[FONT=&quot] 9/5/2017 22:42|KeyReturn 1 0010 Education Set 2Lauchnor, Robert,[/FONT]
[FONT=&quot] 9/5/2017 22:43|KeyReturn 1 0005 Early Ed Set 1 Lauchnor, Robert,[/FONT]
[FONT=&quot] 9/5/2017 22:44|KeyReturn 1 0013 1037 FM Master Kimball, Dan [/FONT]
[FONT=&quot] 9/5/2017 23:51|KeyReturn 1 0002 Team Lead Key Kimball, Dan [/FONT]
[FONT=&quot] 9/6/2017 03:51|KeyRemoval 1 0001 Coordinator KeyNyman, Leif, Leif[/FONT]
[FONT=&quot] 9/6/2017 04:22|KeyRemoval 1 0010 Education Set 2Beutler, Tammy, T[/FONT]
[FONT=&quot] 9/6/2017 04:22|KeyRemoval 1 0006 Early Ed Set 2 Nyman, Leif, Leif[/FONT]
[FONT=&quot] 9/6/2017 07:05|KeyRemoval 1 0004 Edith Bowen SetMartin, Vicki [/FONT]
[FONT=&quot] 9/6/2017 07:21|KeyReturn 1 0010 Education Set 2Beutler, Tammy, T[/FONT]
[FONT=&quot] 9/6/2017 09:54|KeyRemoval 1 0003 Edith BowenSet Essman, Layna [/FONT]
[FONT=&quot] 9/6/2017 11:11|KeyReturn 1 0006 Early Ed Set 2 Nyman, Leif, Leif[/FONT]
[FONT=&quot] 9/6/2017 12:53|KeyReturn 1 0001 Coordinator KeyNyman, Leif, Leif[/FONT]
[FONT=&quot] 9/6/2017 14:33|KeyReturn 1 0004 Edith Bowen SetMartin, Vicki [/FONT]
[FONT=&quot] 9/6/2017 14:50|KeyReturn 1 0003 Edith BowenSet Essman, Layna [/FONT]
[FONT=&quot] 9/6/2017 15:20|KeyRemoval 1 0002 Team Lead Key Kimball, Dan [/FONT]
[FONT=&quot] 9/6/2017 16:00|KeyRemoval 1 0004 Edith Bowen SetEliason, Joseph, [/FONT]
[FONT=&quot] 9/6/2017 16:43|KeyRemoval 1 0003 Edith BowenSet Essman, Ariana, A[/FONT]
[FONT=&quot] 9/6/2017 17:41|Key Return 1 0004 Edith Bowen SetEliason, Joseph, [/FONT]
[FONT=&quot] 9/6/2017 18:00|KeyReturn 1 0003 Edith BowenSet Essman, Ariana, A[/FONT]
[FONT=&quot] 9/6/2017 18:01|KeyRemoval 1 0006 Early Ed Set 2 Essman, Ariana, A[/FONT]
[FONT=&quot] 9/6/2017 18:56|KeyRemoval 1 0010 Education Set 2Lauchnor, Robert,[/FONT]
[FONT=&quot] 9/6/2017 18:56|KeyRemoval 1 0005 Early Ed Set 1 Nel, Jared, Jared[/FONT]
[FONT=&quot] 9/6/2017 19:17|KeyRemoval 1 0013 1037 FM Master Kimball, Dan [/FONT]
[FONT=&quot] 9/6/2017 19:37|KeyRemoval 1 0004 Edith Bowen SetEliason, Joseph, [/FONT]
[FONT=&quot] 9/6/2017 22:46|KeyReturn 1 0010 Education Set 2Lauchnor, Robert,[/FONT]
[FONT=&quot] 9/6/2017 22:46|KeyReturn 1 0005 Early Ed Set 1 Nel, Jared, Jared[/FONT]
[FONT=&quot] 9/6/2017 22:56|KeyReturn 1 0013 1037 FM Master Kimball, Dan [/FONT]
[FONT=&quot] 9/6/2017 23:15|KeyReturn 1 0004 Edith Bowen SetEliason, Joseph, [/FONT]
[FONT=&quot] 9/6/2017 23:47|KeyReturn 1 0002 Team Lead Key Kimball, Dan [/FONT]
[FONT=&quot] 9/7/2017 00:37|KeyReturn 1 0006 Early Ed Set 2 Nel, Jared, Jared[/FONT]
[FONT=&quot] 9/7/2017 04:10|KeyRemoval 1 0006 Early Ed Set 2 Hull, Julie, Juli[/FONT]
[FONT=&quot] 9/7/2017 04:10|KeyRemoval 1 0010 Education Set 2Beutler, Tammy, T[/FONT]
[FONT=&quot] 9/7/2017 04:11|KeyRemoval 1 0005 Early Ed Set 1 Harrison, Jakend,[/FONT]
[FONT=&quot] 9/7/2017 04:11|KeyRemoval 1 0001 Coordinator KeyNyman, Leif, Leif[/FONT]
[FONT=&quot] 9/7/2017 07:07|KeyRemoval 1 0004 Edith Bowen SetMartin, Vicki [/FONT]
[FONT=&quot] 9/7/2017 07:47|KeyReturn 1 0010 Education Set 2Beutler, Tammy, T[/FONT]
[FONT=&quot] 9/7/2017 08:08|KeyReturn 1 0006 Early Ed Set 2 Harrison, Jakend,[/FONT]
[FONT=&quot] 9/7/2017 08:08|KeyReturn 1 0005 Early Ed Set 1 Harrison, Jakend,[/FONT]
[FONT=&quot] 9/7/2017 08:20|KeyReturn 1 0001 Coordinator KeyNyman, Leif, Leif[/FONT]
[FONT=&quot] 9/7/2017 09:57|KeyRemoval 1 0003 Edith BowenSet Essman, Layna [/FONT]
[FONT=&quot] 9/7/2017 14:37|Key Return 1 0004 Edith Bowen SetMartin, Vicki [/FONT]
[FONT=&quot] 9/7/2017 14:52|KeyReturn 1 0003 Edith BowenSet Essman, Layna [/FONT]
[FONT=&quot] 9/7/2017 16:13|KeyRemoval 1 0002 Team Lead Key Kimball, Dan [/FONT]
[FONT=&quot] 9/7/2017 17:03|KeyRemoval 1 0005 Early Ed Set 1 Nel, Jared, Jared[/FONT]
[FONT=&quot] 9/7/2017 17:51|KeyRemoval 1 0010 Education Set 2Beutler, Tammy, T[/FONT]
[FONT=&quot] 9/7/2017 18:38|KeyRemoval 1 0006 Early Ed Set 2 Essman, Ariana, A[/FONT]
[FONT=&quot] 9/7/2017 18:47|KeyRemoval 1 0013 1037 FM Master Kimball, Dan [/FONT]
[FONT=&quot] 9/7/2017 19:05|KeyRemoval 1 0003 Edith BowenSet Essman, Ariana, A[/FONT]
[FONT=&quot] 9/7/2017 19:13|KeyRemoval 1 0009 Education Set 1Lauchnor, Robert,[/FONT]
[FONT=&quot] 9/7/2017 20:06|KeyReturn 1 0003 Edith BowenSet Essman, Ariana, A[/FONT]
[FONT=&quot] 9/7/2017 22:30|KeyReturn 1 0006 Early Ed Set 2 Essman, Ariana, A[/FONT]
[FONT=&quot] 9/7/2017 22:31|KeyReturn 1 0005 Early Ed Set 1 Nel, Jared, Jared[/FONT]
[FONT=&quot] 9/7/2017 22:32|KeyReturn 1 0009 Education Set 1Lauchnor, Robert,[/FONT]
[FONT=&quot] 9/7/2017 22:39|KeyReturn 1 0013 1037 FM Master Kimball, Dan [/FONT]
[FONT=&quot] 9/7/2017 23:45|KeyReturn 1 0010 Education Set 2Beutler, Tammy, T[/FONT]
[FONT=&quot] 9/7/2017 23:51|KeyReturn 1 0002 Team Lead Key Kimball, Dan [/FONT]
[FONT=&quot] 9/8/2017 04:09|KeyRemoval 1 0005 Early Ed Set 1 Harrison, Jakend,[/FONT]
[FONT=&quot] 9/8/2017 08:38|KeyRemoval 1 0004 Edith Bowen SetMartin, Vicki [/FONT]
[FONT=&quot] 9/8/2017 09:57|KeyRemoval 1 0003 Edith BowenSet Essman, Layna [/FONT]
[FONT=&quot] 9/8/2017 09:59|KeyReturn 1 0005 Early Ed Set 1 Harrison, Jakend,[/FONT]
[FONT=&quot] 9/8/2017 14:25|KeyReturn 1 0004 Edith Bowen SetMartin, Vicki [/FONT]
[FONT=&quot] 9/8/2017 14:35|KeyReturn 1 0003 Edith BowenSet Essman, Layna [/FONT]
[FONT=&quot] 9/10/2017 15:26|KeyRemoval 1 0002 Team Lead Key Kimball, Dan [/FONT]
[FONT=&quot] 9/10/2017 16:53|KeyRemoval 1 0005 Early Ed Set 1 Nel, Jared, Jared[/FONT]
[FONT=&quot] 9/10/2017 19:00|KeyRemoval 1 0010 Education Set 2Lauchnor, Robert,[/FONT]
[FONT=&quot] 9/10/2017 19:11|KeyRemoval 1 0013 1037 FM Master Kimball, Dan [/FONT]
[FONT=&quot] 9/10/2017 22:44|Key Return 1 0010 Education Set 2Lauchnor, Robert,[/FONT]
[FONT=&quot] 9/10/2017 22:44|KeyReturn 1 0005 Early Ed Set 1 Nel, Jared, Jared[/FONT]
[FONT=&quot] 9/10/2017 23:48|KeyReturn 1 0002 Team Lead Key Kimball, Dan [/FONT]
[FONT=&quot] 9/10/2017 23:48|KeyReturn 1 0013 1037 FM Master Kimball, Dan [/FONT]
[FONT=&quot] 9/11/2017 04:01|KeyRemoval 1 0005 Early Ed Set 1 Harrison, Jakend,[/FONT]
[FONT=&quot] 9/11/2017 04:02|KeyRemoval 1 0006 Early Ed Set 2 Hull, Julie, Juli[/FONT]
[FONT=&quot] 9/11/2017 04:03|KeyRemoval 1 0001 Coordinator KeyNyman, Leif, Leif[/FONT]
[FONT=&quot] 9/11/2017 07:04|KeyRemoval 1 0004 Edith Bowen SetMartin, Vicki [/FONT]
[FONT=&quot] 9/11/2017 08:01|KeyReturn 1 0005 Early Ed Set 1 Harrison, Jakend,[/FONT]
[FONT=&quot] 9/11/2017 08:46|KeyReturn 1 0006 Early Ed Set 2 Nyman, Leif, Leif[/FONT]
[FONT=&quot] 9/11/2017 09:56|KeyRemoval 1 0003 Edith BowenSet Essman, Layna [/FONT]
[FONT=&quot] 9/11/2017 12:34|KeyReturn 1 0001 Coordinator KeyNyman, Leif, Leif[/FONT]
[FONT=&quot] 9/11/2017 14:10|KeyReturn 1 0004 Edith Bowen SetMartin, Vicki [/FONT]
[FONT=&quot] 9/11/2017 14:10|KeyReturn 1 0003 Edith BowenSet Essman, Layna [/FONT]
[FONT=&quot] 9/11/2017 15:24|KeyRemoval 1 0002 Team Lead Key Kimball, Dan [/FONT]
[FONT=&quot] 9/11/2017 18:52|KeyRemoval 1 0005 Early Ed Set 1 Nel, Jared, Jared[/FONT]
[FONT=&quot] 9/11/2017 19:09|KeyRemoval 1 0009 Education Set 1Kimball, Dan [/FONT]
[FONT=&quot] 9/11/2017 19:10|KeyRemoval 1 0004 Edith Bowen SetEliason, Joseph, [/FONT]
[FONT=&quot] 9/11/2017 20:07|KeyRemoval 1 0003 Edith BowenSet Kimball, Dan [/FONT]
[FONT=&quot] 9/11/2017 20:38|KeyRemoval 1 0010 Education Set 2Lauchnor, Robert,[/FONT]
[FONT=&quot] 9/11/2017 21:03|KeyReturn 1 0009 Education Set 1Kimball, Dan [/FONT]
[FONT=&quot] 9/11/2017 21:03|KeyRemoval 1 0013 1037 FM Master Kimball, Dan [/FONT]
[FONT=&quot] 9/11/2017 22:42|KeyReturn 1 0010 Education Set 2Lauchnor, Robert,[/FONT]
[FONT=&quot] 9/11/2017 22:43|Key Return 1 0005 Early Ed Set 1 Nel, Jared, Jared[/FONT]
[FONT=&quot] 9/11/2017 22:44|KeyReturn 1 0003 Edith BowenSet Kimball, Dan [/FONT]
[FONT=&quot] 9/11/2017 22:46|KeyReturn 1 0013 1037 FM Master Kimball, Dan [/FONT]
[FONT=&quot] 9/11/2017 22:52|KeyReturn 1 0004 Edith Bowen SetEliason, Joseph, [/FONT]
[FONT=&quot] 9/11/2017 23:45|KeyReturn 1 0002 Team Lead Key Kimball, Dan [/FONT]
[FONT=&quot] 9/12/2017 04:03|KeyRemoval 1 0010 Education Set 2Beutler, Tammy, T[/FONT]
[FONT=&quot] 9/12/2017 04:04|KeyRemoval 1 0006 Early Ed Set 2 Hull, Julie, Juli[/FONT]
[FONT=&quot] 9/12/2017 04:04|KeyRemoval 1 0005 Early Ed Set 1 Harrison, Jakend,[/FONT]
[FONT=&quot] 9/12/2017 04:05|KeyRemoval 1 0001 Coordinator KeyNyman, Leif, Leif[/FONT]
[FONT=&quot] 9/12/2017 07:13|KeyRemoval 1 0004 Edith Bowen SetMartin, Vicki [/FONT]
[FONT=&quot] 9/12/2017 07:43|KeyReturn 1 0010 Education Set 2Beutler, Tammy, T[/FONT]
[FONT=&quot] 9/12/2017 08:08|KeyReturn 1 0005 Early Ed Set 1 Harrison, Jakend,[/FONT]
[FONT=&quot] 9/12/2017 09:30|KeyReturn 1 0006 Early Ed Set 2 Hull, Julie, Juli[/FONT]
[FONT=&quot] 9/12/2017 09:53|KeyRemoval 1 0003 Edith BowenSet Essman, Layna [/FONT]
[FONT=&quot] 9/12/2017 12:16|KeyReturn 1 0001 Coordinator KeyNyman, Leif, Leif[/FONT]
[FONT=&quot] 9/12/2017 13:29|KeyReturn 1 0004 Edith Bowen SetMartin, Vicki [/FONT]
[FONT=&quot] 9/12/2017 13:57|KeyReturn 1 0003 Edith BowenSet Essman, Layna [/FONT]
[FONT=&quot] 9/12/2017 18:38|KeyRemoval 1 0006 Early Ed Set 2 Essman, Ariana, A[/FONT]
[FONT=&quot] 9/12/2017 18:54|KeyRemoval 1 0005 Early Ed Set 1 Nel, Jared, Jared[/FONT]
[FONT=&quot] 9/12/2017 18:54|KeyRemoval 1 0009 Education Set 1Nel, Jared, Jared[/FONT]
[FONT=&quot] 9/12/2017 19:04|KeyRemoval 1 0003 Edith BowenSet Fortney, John, Jo[/FONT]
[FONT=&quot] 9/12/2017 19:06|KeyReturn 1 0003 Edith BowenSet Fortney, John, Jo[/FONT]
[FONT=&quot] 9/12/2017 19:06|KeyRemoval 1 0004 Edith Bowen SetFortney, John, Jo[/FONT]
[FONT=&quot] 9/12/2017 19:07|KeyReturn 1 0004 Edith Bowen SetFortney, John, Jo[/FONT]
[FONT=&quot] 9/12/2017 19:08|KeyRemoval 1 0013 1037 FM Master Fortney, John, Jo[/FONT]
[FONT=&quot] 9/12/2017 19:43|KeyRemoval 1 0004 Edith Bowen SetEliason, Joseph, [/FONT]
[FONT=&quot] 9/12/2017 22:44|Key Return 1 0006 Early Ed Set 2 Essman, Ariana, A[/FONT]
[FONT=&quot] 9/12/2017 22:44|KeyReturn 1 0009 Education Set 1Essman, Ariana, A[/FONT]
[FONT=&quot] 9/12/2017 22:45|KeyReturn 1 0005 Early Ed Set 1 Nel, Jared, Jared[/FONT]
[FONT=&quot] 9/12/2017 22:51|KeyReturn 1 0013 1037 FM Master Fortney, John, Jo[/FONT]
[FONT=&quot] 9/12/2017 22:55|KeyReturn 1 0004 Edith Bowen SetEliason, Joseph, [/FONT]
[FONT=&quot] 9/13/2017 04:03|KeyRemoval 1 0005 Early Ed Set 1 Harrison, Jakend,[/FONT]
[FONT=&quot] 9/13/2017 04:04|KeyRemoval 1 0010 Education Set 2Nyman, Leif, Leif[/FONT]
[FONT=&quot] 9/13/2017 04:04|KeyReturn 1 0010 Education Set 2Nyman, Leif, Leif[/FONT]
[FONT=&quot] 9/13/2017 04:05|KeyRemoval 1 0010 Education Set 2Nyman, Leif, Leif[/FONT]
[FONT=&quot] 9/13/2017 04:06|KeyRemoval 1 0009 Education Set 1Beutler, Tammy, T[/FONT]
[FONT=&quot] 9/13/2017 04:06|KeyRemoval 1 0006 Early Ed Set 2 Hull, Julie, Juli[/FONT]
[FONT=&quot] 9/13/2017 04:06|KeyRemoval 1 0001 Coordinator KeyNyman, Leif, Leif[/FONT]
[FONT=&quot] 9/13/2017 07:42|KeyReturn 1 0010 Education Set 2Beutler, Tammy, T[/FONT]
[FONT=&quot] 9/13/2017 08:04|KeyReturn 1 0005 Early Ed Set 1 Harrison, Jakend,[/FONT]
[FONT=&quot] 9/13/2017 09:15|KeyReturn 1 0006 Early Ed Set 2 Nyman, Leif, Leif[/FONT]
[FONT=&quot] 9/13/2017 10:01|KeyRemoval 1 0003 Edith BowenSet Essman, Layna [/FONT]
[FONT=&quot] 9/13/2017 10:07|KeyReturn 1 0009 Education Set 1Nyman, Leif, Leif[/FONT]
[FONT=&quot] 9/13/2017 10:36|KeyReturn 1 0001 Coordinator KeyNyman, Leif, Leif[/FONT]
[FONT=&quot] 9/13/2017 14:33|KeyReturn 1 0003 Edith BowenSet Essman, Layna [/FONT]
[FONT=&quot] 9/13/2017 15:39|KeyRemoval 1 0002 Team Lead Key Kimball, Dan [/FONT]
[FONT=&quot] 9/13/2017 16:36|KeyRemoval 1 0003 Edith BowenSet Essman, Ariana, A[/FONT]
[FONT=&quot] 9/13/2017 18:04|KeyReturn 1 0003 Edith BowenSet Essman, Ariana, A[/FONT]
[FONT=&quot] 9/13/2017 18:04|KeyRemoval 1 0006 Early Ed Set 2 Essman, Ariana, A[/FONT]
[FONT=&quot] 9/13/2017 18:06|KeyRemoval 1 0005 Early Ed Set 1 Essman, Ariana, A[/FONT]
[FONT=&quot] 9/13/2017 18:08|KeyReturn 1 0005 Early Ed Set 1 Essman, Ariana, A[/FONT]
[FONT=&quot] 9/13/2017 18:55|KeyRemoval 1 0010 Education Set 2Lauchnor, Robert,[/FONT]
[FONT=&quot] 9/13/2017 18:56|KeyRemoval 1 0004 Edith Bowen SetEliason, Joseph, [/FONT]
[FONT=&quot] 9/13/2017 18:56|KeyRemoval 1 0005 Early Ed Set 1 Nel, Jared, Jared[/FONT]
[FONT=&quot] 9/13/2017 19:15|KeyRemoval 1 0013 1037 FM Master Fortney, John, Jo[/FONT]
[FONT=&quot] 9/13/2017 22:47|KeyReturn 1 0010 Education Set 2Lauchnor, Robert,[/FONT]
[FONT=&quot] 9/13/2017 22:48|KeyReturn 1 0006 Early Ed Set 2 Nel, Jared, Jared[/FONT]
[FONT=&quot] 9/13/2017 22:48|KeyReturn 1 0005 Early Ed Set 1 Nel, Jared, Jared[/FONT]
[FONT=&quot] 9/13/2017 22:49|KeyReturn 1 0013 1037 FM Master Fortney, John, Jo[/FONT]
[FONT=&quot] 9/13/2017 22:58|KeyReturn 1 0004 Edith Bowen SetEliason, Joseph, [/FONT]
[FONT=&quot] 9/13/2017 23:52|KeyReturn 1 0002 Team Lead Key Kimball, Dan [/FONT]
[FONT=&quot] 9/14/2017 03:59|KeyRemoval 1 0005 Early Ed Set 1 Harrison, Jakend,[/FONT]
[FONT=&quot] 9/14/2017 04:00|KeyRemoval 1 0006 Early Ed Set 2 Hull, Julie, Juli[/FONT]
[FONT=&quot] 9/14/2017 04:01|KeyRemoval 1 0009 Education Set 1Harrison, Jakend,[/FONT]
[FONT=&quot] 9/14/2017 04:07|KeyRemoval 1 0001 Coordinator KeyNyman, Leif, Leif[/FONT]
[FONT=&quot] 9/14/2017 07:10|KeyRemoval 1 0004 Edith Bowen SetMartin, Vicki [/FONT]
[FONT=&quot] 9/14/2017 07:46|KeyReturn 1 0006 Early Ed Set 2 Hull, Julie, Juli[/FONT]
[FONT=&quot] 9/14/2017 09:57|KeyRemoval 1 0003 Edith BowenSet Essman, Layna [/FONT]
[FONT=&quot] 9/14/2017 11:37|KeyReturn 1 0001 Coordinator KeyNyman, Leif, Leif[/FONT]
[FONT=&quot] 9/14/2017 11:37|KeyReturn 1 0005 Early Ed Set 1 Nyman, Leif, Leif[/FONT]
[FONT=&quot] 9/14/2017 11:37|KeyReturn 1 0009 Education Set 1Nyman, Leif, Leif[/FONT]
[FONT=&quot] 9/14/2017 13:02|KeyReturn 1 0004 Edith Bowen SetMartin, Vicki [/FONT]
[FONT=&quot] 9/14/2017 13:15|Key Return 1 0003 Edith BowenSet Essman, Layna [/FONT]
[FONT=&quot] 9/14/2017 16:50|KeyRemoval 1 0005 Early Ed Set 1 Nel, Jared, Jared[/FONT]
[FONT=&quot] 9/14/2017 18:24|KeyRemoval 1 0002 Team Lead Key Kimball, Dan [/FONT]
[FONT=&quot] 9/14/2017 18:46|KeyRemoval 1 0013 1037 FM Master Fortney, John, Jo[/FONT]
[FONT=&quot] 9/14/2017 18:54|KeyRemoval 1 0006 Early Ed Set 2 Essman, Ariana, A[/FONT]
[FONT=&quot] 9/14/2017 18:58|KeyRemoval 1 0010 Education Set 2Lauchnor, Robert,[/FONT]
[FONT=&quot] 9/14/2017 18:59|KeyRemoval 1 0004 Edith Bowen SetEliason, Joseph, [/FONT]
[FONT=&quot] 9/14/2017 22:39|KeyReturn 1 0006 Early Ed Set 2 Essman, Ariana, A[/FONT]
[FONT=&quot] 9/14/2017 22:40|KeyReturn 1 0005 Early Ed Set 1 Nel, Jared, Jared[/FONT]
[FONT=&quot] 9/14/2017 22:40|KeyReturn 1 0010 Education Set 2Lauchnor, Robert,[/FONT]
[FONT=&quot] 9/14/2017 22:51|KeyReturn 1 0013 1037 FM Master Fortney, John, Jo[/FONT]
[FONT=&quot] 9/14/2017 22:52|KeyReturn 1 0004 Edith Bowen SetEliason, Joseph, [/FONT]
[FONT=&quot] 9/15/2017 00:06|KeyReturn 1 0002 Team Lead Key Kimball, Dan [/FONT]
[FONT=&quot] 9/15/2017 03:42|KeyRemoval 1 0001 Coordinator KeyNyman, Leif, Leif[/FONT]
[FONT=&quot] 9/15/2017 04:06|KeyRemoval 1 0010 Education Set 2Beutler, Tammy, T[/FONT]
[FONT=&quot] 9/15/2017 04:08|KeyRemoval 1 0005 Early Ed Set 1 Nyman, Leif, Leif[/FONT]
[FONT=&quot] 9/15/2017 04:08|KeyRemoval 1 0009 Education Set 1Nyman, Leif, Leif[/FONT]
[FONT=&quot] 9/15/2017 04:10|KeyRemoval 1 0006 Early Ed Set 2 Hull, Julie, Juli[/FONT]
[FONT=&quot] 9/15/2017 07:08|KeyRemoval 1 0004 Edith Bowen SetMartin, Vicki [/FONT]
[FONT=&quot] 9/15/2017 07:45|KeyReturn 1 0010 Education Set 2Beutler, Tammy, T[/FONT]
[FONT=&quot] 9/15/2017 08:22|KeyReturn 1 0006 Early Ed Set 2 Nyman, Leif, Leif[/FONT]
[FONT=&quot] 9/15/2017 09:56|KeyRemoval 1 0003 Edith BowenSet Essman, Layna [/FONT]
[FONT=&quot] 9/15/2017 12:04|KeyReturn 1 0005 Early Ed Set 1 Nyman, Leif, Leif[/FONT]
[FONT=&quot] 9/15/2017 12:04|KeyReturn 1 0001 Coordinator KeyNyman, Leif, Leif[/FONT]
[FONT=&quot] 9/15/2017 12:04|KeyReturn 1 0009 Education Set 1Nyman, Leif, Leif[/FONT]
[FONT=&quot] 9/15/2017 13:07|KeyReturn 1 0004 Edith Bowen SetMartin, Vicki [/FONT]
[FONT=&quot] 9/15/2017 13:15|KeyReturn 1 0003 Edith BowenSet Essman, Layna [/FONT]
[FONT=&quot] 9/17/2017 15:18|KeyRemoval 1 0002 Team Lead Key Kimball, Dan [/FONT]
[FONT=&quot] 9/17/2017 16:58|KeyRemoval 1 0005 Early Ed Set 1 Nel, Jared, Jared[/FONT]
[FONT=&quot] 9/17/2017 19:05|KeyRemoval 1 0013 1037 FM Master Fortney, John, Jo[/FONT]
[FONT=&quot] 9/17/2017 19:38|KeyRemoval 1 0010 Education Set 2Lauchnor, Robert,[/FONT]
[FONT=&quot] 9/17/2017 22:33|KeyReturn 1 0013 1037 FM Master Fortney, John, Jo[/FONT]
[FONT=&quot] 9/17/2017 22:33|ALARM:Problem Key 1 0004 Edith Bowen SetFortney, John, Jo[/FONT]
[FONT=&quot] 9/17/2017 22:40|KeyReturn 1 0004 Edith Bowen SetNel, Jared, Jared[/FONT]
[FONT=&quot] 9/17/2017 22:40|KeyReturn 1 0005 Early Ed Set 1 Nel, Jared, Jared[/FONT]
[FONT=&quot] 9/17/2017 22:45|KeyReturn 1 0010 Education Set 2Lauchnor, Robert,[/FONT]
[FONT=&quot] 9/17/2017 22:45|ALARM:Problem Key 1 0004 Edith Bowen SetLauchnor, Robert,[/FONT]
[FONT=&quot] 9/17/2017 23:49|KeyReturn 1 0004 Edith Bowen SetKimball, Dan [/FONT]
[FONT=&quot] 9/17/2017 23:49|KeyReturn 1 0002 Team Lead Key Kimball, Dan [/FONT]
[FONT=&quot] 9/18/2017 03:59|KeyRemoval 1 0010 Education Set 2Beutler, Tammy, T[/FONT]
[FONT=&quot] 9/18/2017 04:01|Key Removal 1 0006 Early Ed Set 2 Hull, Julie, Juli[/FONT]
[FONT=&quot] 9/18/2017 04:01|KeyRemoval 1 0005 Early Ed Set 1 Harrison, Jakend,[/FONT]
[FONT=&quot] 9/18/2017 04:02|KeyRemoval 1 0009 Education Set 1Harrison, Jakend,[/FONT]
[FONT=&quot] 9/18/2017 07:05|KeyRemoval 1 0004 Edith Bowen SetMartin, Vicki [/FONT]
[FONT=&quot] 9/18/2017 07:31|KeyReturn 1 0010 Education Set 2Beutler, Tammy, T[/FONT]
[FONT=&quot] 9/18/2017 07:59|KeyReturn 1 0005 Early Ed Set 1 Harrison, Jakend,[/FONT]
[FONT=&quot] 9/18/2017 07:59|KeyReturn 1 0009 Education Set 1Harrison, Jakend,[/FONT]
[FONT=&quot] 9/18/2017 07:59|KeyReturn 1 0006 Early Ed Set 2 Harrison, Jakend,[/FONT]
[FONT=&quot] 9/18/2017 09:56|KeyRemoval 1 0003 Edith BowenSet Essman, Layna [/FONT]
[FONT=&quot] 9/18/2017 14:00|KeyReturn 1 0003 Edith BowenSet Essman, Layna [/FONT]
[FONT=&quot] 9/18/2017 14:45|KeyReturn 1 0004 Edith Bowen SetMartin, Vicki [/FONT]
[FONT=&quot] 9/18/2017 15:20|KeyRemoval 1 0002 Team Lead Key Kimball, Dan [/FONT]
[FONT=&quot] 9/18/2017 18:57|KeyRemoval 1 0005 Early Ed Set 1 Nel, Jared, Jared[/FONT]
[FONT=&quot] 9/18/2017 19:34|KeyRemoval 1 0010 Education Set 2Lauchnor, Robert,[/FONT]
[FONT=&quot] 9/18/2017 19:47|KeyRemoval 1 0013 1037 FM Master Fortney, John, Jo[/FONT]
[FONT=&quot] 9/18/2017 19:56|KeyRemoval 1 0006 Early Ed Set 2 Essman, Ariana, A[/FONT]
[FONT=&quot] 9/18/2017 22:43|KeyReturn 1 0006 Early Ed Set 2 Essman, Ariana, A[/FONT]
[FONT=&quot] 9/18/2017 22:44|KeyReturn 1 0010 Education Set 2Lauchnor, Robert,[/FONT]
[FONT=&quot] 9/18/2017 22:44|KeyReturn 1 0005 Early Ed Set 1 Nel, Jared, Jared[/FONT]
[FONT=&quot] 9/18/2017 22:45|KeyRemoval 1 0004 Edith Bowen SetEliason, Joseph, [/FONT]
[FONT=&quot] 9/18/2017 22:46|KeyReturn 1 0013 1037 FM Master Fortney, John, Jo[/FONT]
[FONT=&quot] 9/18/2017 23:55|KeyReturn 1 0002 Team Lead Key Kimball, Dan [/FONT]
[FONT=&quot] 9/18/2017 23:55|KeyReturn 1 0004 Edith Bowen SetEliason, Joseph, [/FONT]
[FONT=&quot] 9/19/2017 04:07|KeyRemoval 1 0010 Education Set 2Beutler, Tammy, T[/FONT]
[FONT=&quot] 9/19/2017 04:08|KeyRemoval 1 0006 Early Ed Set 2 Hull, Julie, Juli[/FONT]
[FONT=&quot] 9/19/2017 04:08|KeyRemoval 1 0009 Education Set 1Nyman, Leif, Leif[/FONT]
[FONT=&quot] 9/19/2017 04:08|KeyRemoval 1 0001 Coordinator KeyNyman, Leif, Leif[/FONT]
[FONT=&quot] 9/19/2017 05:51|KeyRemoval 1 0005 Early Ed Set 1 Burk, Michael [/FONT]
[FONT=&quot] 9/19/2017 06:11|KeyReturn 1 0001 Coordinator KeyNyman, Leif, Leif[/FONT]
[FONT=&quot] 9/19/2017 07:13|KeyReturn 1 0010 Education Set 2Beutler, Tammy, T[/FONT]
[FONT=&quot] 9/19/2017 07:32|KeyRemoval 1 0004 Edith Bowen SetMartin, Vicki [/FONT]
[FONT=&quot] 9/19/2017 07:47|KeyReturn 1 0005 Early Ed Set 1 Burk, Michael [/FONT]
[FONT=&quot] 9/19/2017 09:04|KeyReturn 1 0006 Early Ed Set 2 Hull, Julie, Juli[/FONT]
[FONT=&quot] 9/19/2017 09:04|KeyReturn 1 0009 Education Set 1Hull, Julie, Juli[/FONT]
[FONT=&quot] 9/19/2017 09:49|KeyRemoval 1 0003 Edith BowenSet Essman, Layna [/FONT]
[FONT=&quot]--------------------------------------------------------------------------------------[/FONT]
[FONT=&quot]Total Records:270[/FONT]
[FONT=&quot]END REPORT[/FONT] <o:p></o:p>
 
Upvote 0
okay, scratch my reply for #4 . the data in each row pastes into one cell in that row. It was doing what I initially stated when it copied it from MS Word, but if I copy/paste it directly from where it comes from then it differentiates the data into different rows, but still pastes the data into one cell in that row,
 
Upvote 0
Leiftinspace said:
I'm just pasting it all here for you to look at and figure out. :) Hopefully this answers questions 1-3.
Click to expand...
Actually, it isn't up to us to figure out what you want, especially when you haven't even shown us for the sample data what the expected results are. ;)
You need to clarify what you want.

And no, the data itself does not answer 2 or 3.
The problem is deciding what goes under "KEY NAME" and what goes under "BYUSER". So..
a) Could you please explain how we decide that issue, and
b) For each of the few examples below, show us [by applying your answer to a) above] what goes in each of the last 2 columns.

Leiftinspace said:
--------------------------------------------------------------------------------------
DATE &TIME | TRANSACTION |BOX|KEY#|KEY NAME | BYUSER
--------------------------------------------------------------------------------------
9/1/2017 04:03|KeyRemoval 1 0007 HSRC Set 1 Burk, Michael
9/1/2017 04:04|KeyRemoval 1 0006 Early Ed Set 2 Nyman, Leif, Leif
9/1/2017 04:04|Key Removal 1 0001 Coordinator KeyNyman, Leif, Leif
9/1/2017 07:11|KeyRemoval 1 0004 Edith Bowen SetMartin, Vicki
9/4/2017 18:53|KeyRemoval 1 0013 1037 FM Master Kimball, Dan
Click to expand...
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

airforceone
  • Solved
Search Row 1 for Text if Found Convert Column to Date
2
Replies
11
Views
802
airforceone
airforceone
M
  • Solved
Help with separating Text String from a Excel Col
Replies
3
Views
414
Peter_SSs
Peter_SSs
Seba Robles
  • Solved
VBA: Remove Range for Dynamic Named Range
Replies
3
Views
805
Fluff
Fluff
S
  • Question
Help on complicated VBA code
Replies
3
Views
641
superfb
S
M
  • Question
Cleaning up Code
Replies
0
Views
668
MHamid
M

Forum statistics

Threads
1,215,523
Messages
6,125,318
Members
449,218
Latest member
Excel Master

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top