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

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

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

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

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

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