auto delimit

[Blake246]

hey guys. I need to copy and paste a line of text into excel everyday. it looks like this 1/24/2015;1.23;1.24;1.26

1/24/2015 1.23 1.24 1.26
1/17/2015 1.20 1.22 1.19

And continues on down. So i move the whole block of data down one row everyday and paste in the new line of text

For a while excel was automatically delimiting the text when I would paste the new line in, but recently it stopped and it pastes the raw text into the first cell. And I have to use the text to column wizard everytime. I can't think of what setting I might have changed to make this happen.

Any thoughts or suggestions?

Thanks

[Blake246]

I can't seem to find anything online to help with this problem.

I have 31 worksheets in one workbook and I have to do the same thing to all of them. I don't want to go through the text to column wizard 31 times a day.

There has to be some way to tell excel to parse the text automatically.

[sourabhg98]

Hello
It would be easy to understand your problem if you can upload your worksheet over here....
This can be done by clicking on the Go Advanced button below the typing area and then paper clip icon--
Regards
Sourabh Gupta

[sourabhg98]

I think you need formulas to separate the text into columns separated by ";"
so suppose you have 1/24/2015;1.23;1.24;1.26 in cell A1
So put these formulas in
B1 =LEFT(A1,FIND("qw",SUBSTITUTE(A1,";","qw",1))-1)
C1 =MID(A1,FIND("qw",SUBSTITUTE(A1,";","qw",1))+1,FIND("qw",SUBSTITUTE(A1,";","qw",2))-FIND("qw",SUBSTITUTE(A1,";","qw",1))-1)
D1 =MID(A1,FIND("qw",SUBSTITUTE(A1,";","qw",2))+1,FIND("qw",SUBSTITUTE(A1,";","qw",3))-FIND("qw",SUBSTITUTE(A1,";","qw",2))-1)
E1 =RIGHT(A1,LEN(A1)-FIND("qw",SUBSTITUTE(A1,";","qw",3)))

Tell me if it works right….

Hope it helps
Do ask for any other query you may have…

If you are satisfied then mark then PLEASE mark this thread as “SOLVED” (by going to thread tools at the top and clicking on “mark this thread as solved”) and you can just click on ADD REPUTATION below my post to say thanks...

Regards
Sourabh Gupta

[AlKey]

Maybe this one

in B1 and pull formula to the right until you see blanks.

=TRIM(MID(SUBSTITUTE(";"&$A1,";",REPT(" ",25)),25*COLUMNS($A:A),25))

Row\Col	A	B	C	D	E
1	1/24/2015;1.23;1.24;1.26	1/24/2015	1.23	1.24	1.26

[Beamernsw]

Hi Blake246, I did a bit of a search for you and it appears it may have something to do with your regional settings, especially with windows 7.
I found that mentioned on a few forums like http://superuser.com/questions/40708...ommas-in-excel

And maybe following what this one says http://www.mrexcel.com/td0129.html you could maybe type a space into the other field