Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spitting data in a single cell
Hi,
I have a report that I need to sort through to be left with raw data that can then be exported into access and manipulated. The problem I'm having is that the report is exported from another program and the data is put into single cells which is no good. I need to split the following into single cells for exporting: Evaluation Title: 14.02.05 @ 11:03:59 Smith Evaluation Date: 22 Feb 05 Evaluator: Lindsey Martin This needs to be split so that i'm left with three cells, i.e. data from Evaluation Title, Evaluation date and Evaluator. I've been working on this for while now and I'm getting nowhere so any help would be greatly appreciated! Cheers Lindsey |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spitting data in a single cell
A single column of data???
If yes, then I think I'd do 3 edit|replaces. #1. Replace: Evaluation Title: with: | #2. Replace: Evaluation Date: with: | #3. Replace: Evaluator: with: | You can use any character in the replacement field that you want--as long as it's not used in the data (Don't use @.) After you do those 3 replacements (replace All), you can select the column of data and do Data|Text to columns. Delimited by | (or whatever character you used) Lindsey M wrote: Hi, I have a report that I need to sort through to be left with raw data that can then be exported into access and manipulated. The problem I'm having is that the report is exported from another program and the data is put into single cells which is no good. I need to split the following into single cells for exporting: Evaluation Title: 14.02.05 @ 11:03:59 Smith Evaluation Date: 22 Feb 05 Evaluator: Lindsey Martin This needs to be split so that i'm left with three cells, i.e. data from Evaluation Title, Evaluation date and Evaluator. I've been working on this for while now and I'm getting nowhere so any help would be greatly appreciated! Cheers Lindsey -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spitting data in a single cell
Hi Dave
No, not a single column of data, it starts with Evaluation Title: 14.02.05 @ 11:03:59 Smith Evaluation Date: 22 Feb 05 Evaluator: Lindsey Martin all in one cell, e.g. A1. I want to split the info so that i have the following B1 = 14.02.05 @ 11:03:09 Smith C1 = 22 Feb 05 D1 = Lindsey Martin And for the original cell (A1) to be cleared or (preferably) deleted. I hope this makes more sense :) Lindsey |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spitting data in a single cell
In A1 you have:
Evaluation Title: 14.02.05 @ 11:03:59 Smith Evaluation Date: 22 Feb 05 Evaluator: Lindsey Martin In B1 enter: =MID(LEFT(A1,SEARCH("Evaluation Date: ",A1)-1),19,LEN(A1)) In C1 enter: =MID(A1,SEARCH("Evaluation Date: ",A1)+17,SEARCH("Evaluator: ",A1)-(SEARCH("Evaluation Date: ",A1)+17)) In D1 enter: =RIGHT(A1,LEN(A1)-SEARCH("Evaluator: ",A1)-10) Copy these formulas down the respective columns as far as needed. "Lindsey M" wrote: Hi Dave No, not a single column of data, it starts with Evaluation Title: 14.02.05 @ 11:03:59 Smith Evaluation Date: 22 Feb 05 Evaluator: Lindsey Martin all in one cell, e.g. A1. I want to split the info so that i have the following B1 = 14.02.05 @ 11:03:09 Smith C1 = 22 Feb 05 D1 = Lindsey Martin And for the original cell (A1) to be cleared or (preferably) deleted. I hope this makes more sense :) Lindsey |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spitting data in a single cell
place the following in your cells
and use copy, paste-value onto itself and so on this will allow for variable name length =TRIM(LEFT(A1,19)) =TRIM(RIGHT(LEFT(A1,LEN(A1)-27),(LEN(LEFT(A1,LEN(A1)-27))-LEN(B1)))) =TRIM(RIGHT(A1,LEN(A1)-(FIND("Date: ",A1)+5))) "Lindsey M" wrote: Hi, I have a report that I need to sort through to be left with raw data that can then be exported into access and manipulated. The problem I'm having is that the report is exported from another program and the data is put into single cells which is no good. I need to split the following into single cells for exporting: Evaluation Title: 14.02.05 @ 11:03:59 Smith Evaluation Date: 22 Feb 05 Evaluator: Lindsey Martin This needs to be split so that i'm left with three cells, i.e. data from Evaluation Title, Evaluation date and Evaluator. I've been working on this for while now and I'm getting nowhere so any help would be greatly appreciated! Cheers Lindsey |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spitting data in a single cell
I was asking if the original data was in one column.
And from your reply, that one cell (A1) isn't enough to tell. (The Data|Text to columns will put things in separate columns.) Lindsey M wrote: Hi Dave No, not a single column of data, it starts with Evaluation Title: 14.02.05 @ 11:03:59 Smith Evaluation Date: 22 Feb 05 Evaluator: Lindsey Martin all in one cell, e.g. A1. I want to split the info so that i have the following B1 = 14.02.05 @ 11:03:09 Smith C1 = 22 Feb 05 D1 = Lindsey Martin And for the original cell (A1) to be cleared or (preferably) deleted. I hope this makes more sense :) Lindsey -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup with two data in a single cell | Excel Worksheet Functions | |||
Email (LDAP) data download into a single Excel cell - data separat | Excel Worksheet Functions | |||
merging data to a single cell | Excel Worksheet Functions | |||
sort data in a single cell? | Excel Programming | |||
Extracting data from a single cell | Excel Programming |