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)"
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.