PDA

View Full Version : [SOLVED] Divide texts into column



snoopies
07-12-2005, 09:12 AM
Hi all,

I've exported a text file to excel , however... I don't know how to divide the texts into column...

The original format looks like...

Monday(morning) (1)
Tuesday(2)
Wednesday(3)
Thursday(4)
Friday(5)
Sat(6)
Sun(7)
Monday(evening) (8)

I want to divide above texts into two column, which looks like:

Col A ---------------- Col B
Monday(morning) ----- 1
Tuesday-------------- 2
Wednesday----------- 3
Thursday------------- 4
Friday---------------- 5
Sat------------------ 6
Sun------------------ 7
Monday(evening) ---- 8

I have another problem here.. I want to use () as a seperator..
but in some cases, e.g monday , it contains 2 sets of ()...
Any suggestions?

Many thanks :)

Bob Phillips
07-12-2005, 09:40 AM
Hi all,

I've exported a text file to excel , however... I don't know how to divide the texts into column...

The original format looks like...

Monday(morning) (1)
Tuesday(2)
Wednesday(3)
Thursday(4)
Friday(5)
Sat(6)
Sun(7)
Monday(evening) (8)

I want to divide above texts into two column, which looks like:

Col A ---------------- Col B
Monday(morning) ----- 1
Tuesday-------------- 2
Wednesday----------- 3
Thursday------------- 4
Friday---------------- 5
Sat------------------ 6
Sun------------------ 7
Monday(evening) ---- 8

I have another problem here.. I want to use () as a seperator..
but in some cases, e.g monday , it contains 2 sets of ()...
Any suggestions?

Many thanks :)

Two formulae

=LEFT(A2,FIND("~",SUBSTITUTE(A2,"(","~",LEN(A2)-LEN(SUBSTITUTE(A2,"(",""))))-1)

and

=MID(A2,FIND("~",SUBSTITUTE(A2,"(","~",LEN(A2)-LEN(SUBSTITUTE(A2,"(",""))))+1,1)

snoopies
07-12-2005, 04:10 PM
Hi Xld,

Thx for your help :)
A little problem here... for the second forlmula..

=MID(A2,FIND("~",SUBSTITUTE(A2,"(","~",LEN(A2)-LEN(SUBSTITUTE(A2,"(",""))))+1,1)

if the value in () has more than 1 digit, it displays the first digit only..
(12) --> 1

Pls advise,thanks!

Bob Phillips
07-12-2005, 05:12 PM
Hi Xld,

Thx for your help :)
A little problem here... for the second forlmula..
=MID(A2,FIND("~",SUBSTITUTE(A2,"(","~",LEN(A2)-LEN(SUBSTITUTE(A2,"(",""))))+1,1)

if the value in () has more than 1 digit, it displays the first digit only..
(12) --> 1

Pls advise,thanks!

Replace the second formula with


=MID(A2,FIND("~",SUBSTITUTE(A2,"(","~",LEN(A2)-LEN(SUBSTITUTE(A2,"(",""))))+1,LEN(A2)-FIND("~",SUBSTITUTE(A2,"(","~",LEN(A2)-LEN(SUBSTITUTE(A2,"(",""))))-1)

snoopies
07-12-2005, 10:59 PM
Thanks!!! :)
It saves me many time......
Thank you soooo much!

excelliot
07-12-2005, 11:23 PM
Replace the second formula with


=MID(A2,FIND("~",SUBSTITUTE(A2,"(","~",LEN(A2)-LEN(SUBSTITUTE(A2,"(",""))))+1,LEN(A2)-FIND("~",SUBSTITUTE(A2,"(","~",LEN(A2)-LEN(SUBSTITUTE(A2,"(",""))))-1)

Can u explain me what this formulae does with"~"
ca n u explain me logic u applied in brief pls.....:help

Bob Phillips
07-13-2005, 02:37 AM
Can u explain me what this formulae does with"~"
ca n u explain me logic u applied in brief pls.....:help

Okay, it works on the principle of extracting the num ber(s) between the (), which could be a variable number of digits, and there could be more than one ( in the formula.

So I start by find the number of brackets

Num_Brackets =LEN(A2)-LEN(SUBSTITUTE(A2,"(",""))

Then I replace the final occurrence of that ( by a unique character, I chose ~, as the number is between the last ()

Mod_Value =SUBSTITUTE(A2,"(","~",num_Brackets)

It is now easy to find where the number, after the (, starts

Num_Start =FIND("~",Mod_Value)+1

So now we know where the number starts, we know the length of the cell (LEN(A2)), and so it is now easy to extract the n umber using the MID function

=MID(A2,Num_Start,LEN(A2)-Num_Start)

snoopies
08-03-2005, 02:21 AM
Hello,

I try to insert the above function into VBA..

sth like :


.Cells(1, 7).Formula = "=LEFT(A2,FIND("~",SUBSTITUTE(A2,"(","~",LEN(A2)-LEN(SUBSTITUTE(A2,"(",""))))-1)"

It shows an error immediately... saying "~" is an invalid character..
Then when I try to change "~" to another symbol, there is another error with "("

Anything's wrong with the above statement?

Thanks.

Bob Phillips
08-03-2005, 03:27 AM
Hello,

I try to insert the above function into VBA..

sth like :


.Cells(1, 7).Formula = "=LEFT(A2,FIND("~",SUBSTITUTE(A2,"(","~",LEN(A2)-LEN(SUBSTITUTE(A2,"(",""))))-1)"

It shows an error immediately... saying "~" is an invalid character..
Then when I try to change "~" to another symbol, there is another error with "("

Anything's wrong with the above statement?

Thanks.

it's the quotes, not the tilde


.Cells(1, 7).Formula = "=LEFT(A2,FIND(""~"",SUBSTITUTE(A2,""("",""~"",LEN(A2)-LEN(SUBSTITUTE(A2,""("",""""))))-1)"

snoopies
08-05-2005, 12:17 AM
Hi,

Thx for help.

And I've got an error for the second statement..


.Cells(1, 7).Formula = "=MID(A5,FIND(""~"",SUBSTITUTE(A5,""("",""~"",LEN(A5)-LEN(SUBSTITUTE(A5,""("",""))))+1,LEN(A5)-FIND(""~"",SUBSTITUTE(A5,""("",""~"",LEN(A5)-LEN(SUBSTITUTE(A5,""("",""))))-1)"

Anything's missing with above sentence?
Thanks again..

hcadar1
08-16-2005, 04:17 AM
Forgot to doublequote at the end... Try this


.Cells(1, 7).Formula = "=MID(A5,FIND(""~"",SUBSTITUTE(A5,""("",""~"",LEN(A5)-LEN(SUBSTITUTE(A5,""("",""""))))+1,LEN(A5)-FIND(""~"",SUBSTITUTE(A5,""("",""~"",LEN(A5)-LEN(SUBSTITUTE(A5,""("",""""))))-1)"