
Master of my heart <3 DopeKitchen

Excel help anyone?
this is probably really easy but i can't work it out
i'm trying to seperate data from one cell into 4... 1st cell will look something like this: elephant 3:35:92 what i want is to copy the text (it may not be just one word) into one cell, and the 3,35 and 92 into a cell each. any ideas? thanks. 
02222009, 08:15 AM 
#1

Advertisement  [Remove Advertisement] 


Quote:
it all depends your data format. Let's say your data is in A1 if your text do not have any spaces then you can use "=left(a1,len(search(" ",a1)1)" for the text and combination of search and mid function for the middle two digits and right command for the last digit
__________________
Yao Ming of Asian Crew Second [M]ember of the 6' Asian club [M]ember of [M] Cadillac Club 04 White Diamond CTS f17881c2d660477b9151fe21fb4fb04b 

02222009, 11:25 AM 
#2

Master of my heart <3 DopeKitchen

Unfortunately the words can have spaces in, and the numbers aren't always formatted the same. they can be x:xx:xx, x:x:x, x:xx:x or x:x:xx. i'm beginning to think there's no automatic way to do this, which is going to cause me problems since i need to seperate >1000 entries
edit: if there's a way to remove the spaces or automatically add a 0 in front of the single digit numbers (eg: x:0x:0x) then that's perfectly acceptible... 
02222009, 11:32 AM 
#3


Assuming the original data is in column A use these formulas.
1st column: =LEFT(A1, FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)LEN(SUBSTITUTE(A1," ",""))))1) 2nd column: =LEFT(RIGHT(A1, LEN(A1)  FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)LEN(SUBSTITUTE(A1," ",""))))), SEARCH(":", RIGHT(A1, LEN(A1)  FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)LEN(SUBSTITUTE(A1," ",""))))))  1) 3rd column: =MID(RIGHT(A1, LEN(A1)  FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)LEN(SUBSTITUTE(A1," ",""))))), SEARCH(":", RIGHT(A1, LEN(A1)  FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)LEN(SUBSTITUTE(A1," ","")))))) +1, SEARCH(":", RIGHT(A1, LEN(A1)  FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)LEN(SUBSTITUTE(A1," ",""))))), SEARCH(":", RIGHT(A1, LEN(A1)  FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)LEN(SUBSTITUTE(A1," ","")))))) +1)  1  SEARCH(":", RIGHT(A1, LEN(A1)  FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)LEN(SUBSTITUTE(A1," ",""))))))) 4th column: =RIGHT(RIGHT(A1, LEN(A1)  FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)LEN(SUBSTITUTE(A1," ",""))))), LEN(RIGHT(A1, LEN(A1)  FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)LEN(SUBSTITUTE(A1," ",""))))))  FIND("@",SUBSTITUTE(RIGHT(A1, LEN(A1)  FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)LEN(SUBSTITUTE(A1," ",""))))),":","@",LEN(RIGHT(A1, LEN(A1)  FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)LEN(SUBSTITUTE(A1," ",""))))))LEN(SUBSTITUTE(RIGHT(A1, LEN(A1)  FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)LEN(SUBSTITUTE(A1," ",""))))),":",""))))) Results...
__________________
comets are like cats, they have tails, and they do what they want 
02222009, 12:47 PM 
#4

Master of my heart <3 DopeKitchen

except when i copy the formula for column 4 it displays the formula instead of the last number. i'll try and work it out myself but i'm a so i probably wont get it 
02222009, 03:34 PM 
#5


__________________
comets are like cats, they have tails, and they do what they want 
02222009, 09:02 PM 
#6

Master of my heart <3 DopeKitchen


02222009, 11:11 PM 
#7


time to upgrade to 2k7
__________________
Yao Ming of Asian Crew Second [M]ember of the 6' Asian club [M]ember of [M] Cadillac Club 04 White Diamond CTS f17881c2d660477b9151fe21fb4fb04b 
02242009, 07:13 PM 
#8


try this one
ColumnB : =IF(MID(A2,SEARCH(":",A2)3,1)=" ",LEFT(A2,SEARCH(":",A2)4),LEFT(A2,SEARCH(":",A2)3)) ColumnC : =MID(A2,LEN(B2)+2,SEARCH(":",A2)(LEN(B2)+2)) ColumnD : =MID(A2,(LEN(B2)+3+LEN(C2)),SEARCH(":",A2,(LEN(B2)+3+LEN(C2) ))(LEN(B2)+3+LEN(C2))) ColumnE : =IF(LEFT(RIGHT(A2,3),1)=":",RIGHT(A2,2),RIGHT(A2,1))
__________________
Yao Ming of Asian Crew Second [M]ember of the 6' Asian club [M]ember of [M] Cadillac Club 04 White Diamond CTS f17881c2d660477b9151fe21fb4fb04b 
02242009, 07:42 PM 
#9


Quote:
seems to work too except for the last column, getting inconsistent results
__________________
comets are like cats, they have tails, and they do what they want 

02242009, 08:28 PM 
#10


__________________
Yao Ming of Asian Crew Second [M]ember of the 6' Asian club [M]ember of [M] Cadillac Club 04 White Diamond CTS f17881c2d660477b9151fe21fb4fb04b 
02242009, 08:33 PM 
#11

Thread Tools  

