Notepad to excel problem

Hi, I am wondering how would one go about turning a notepad file into an excel file without all the data being lumped into one cell?????

for example the following notepad file is in this format:

20060102 190100;139.14;139.2;139.14;139.15;14
20060102 190200;139.19;139.19;139.16;139.18;16
20060102 190300;139.19;139.2;139.17;139.17;16
20060102 190400;139.16;139.24;139.16;139.2;22
20060102 190500;139.19;139.22;139.19;139.21;7

and I would like it in an excel file in the same format but with each group of digits seperated by a cell:


so for example my first line......

20060102 190100;139.14;139.2;139.14;139.15;14

I would like 20060102 in its own cell, followed by 190100 in its own cell, followed by
;139.14, followed by ;139.15, followed by ;14


How would I go about doing this???

with the file open in Excel, select the cells in column A, then click Text to Columns on the Data ribbon.
in the following dialog select "delimited", click next, tick semicolon and space and click finish.

Hi,

please don't quote whole posts. It's just clutter. There's no need to quote at all if you're responding to the last post directly.

If you want to keep the semicolons, you need to give Excel another delimiter.

So, open the text file in Excel,
click Find and Select on the home ribbon, then Replace
in the Find what box type a semicolon -- ;
in the Replace with box type a pipe symbol -- | or an asterisk -- *
Click Replace all
Now select all your data in column A, go to the Text to Columns dialog - Delimited - tick space and "Other" and enter the pipe symbol or the asterisk symbol as the other delimiter symbol, depending on what you choose above.

This will separate your data into a new cell when there is a space or a pipe/asterisk and you will retain the semicolons.

Does that work for you?

Sorry, I was not clear enough above. You want to replace each ; with a *; Then your text will look like this

20060102 190100*;139.14*;139.2*;139.14*;139.15*;14
20060102 190200*;139.19*;139.19*;139.16*;139.18*;16
20060102 190300*;139.19*;139.2*;139.17*;139.17*;16
20060102 190400*;139.16*;139.24*;139.16*;139.2*;22
20060102 190500*;139.19*;139.22*;139.19*;139.21*;7

Then you use text to columns with the delimiters space and asterisk, but not semicolon. This way, the semicolon will be retained.

I'm sure I did not mess it up twice. Replace

;

with

*;

Do you see the semicolon next to the *? Also in the text example above? When you use text to columns with the * and the space as delimiters on the text above, you will retain the semicolon.

Hi, ok by replacing the semicolon with an asteriks I end up with no semicolons, just asteriks?? (because they have been completely replaced)

I have followed your instructions and am not retaining the semicolons.

also, in your example above, wouldnt that be adding the asteriks? (and not replacing the semicolon)?

Hi teylyn, thanks for your help.

I got your original idea working by looking at your example and trying to replicate it.

How did I get it to looks like yours? When I replaced the semicolons I had to replace them with an asteriks and a semicolon. Then it was all smooth sailing from there

woah!
just saw your last post and realised thats what you told me!!!

k, i feel dumb now but thanks anyway

When I replaced the semicolons I had to replace them with an asteriks and a semicolon.

That's what I've been saying from post #6 onwards. Were you listening at all?

I am going to replace the semicolons with 2 semicolons

That will not work. If you select the semicolon as the delimiter, any number of semicolons will be replaced. You need a semicolon and another character, e.g. the *