General [M]ayhem

Go Back   General [M]ayhem > Real Time Sub-Forums > CompuGlobalHyperMegaNet
Register Members List Mark Forums Read [M]erchandise Calendar

Reply
 
Thread Tools
apaininthebutt
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.
Old 02-22-2009, 09:15 AM apaininthebutt is offline  
Reply With Quote
#1  

Advertisement [Remove Advertisement]

ph00ny
 
Quote:
Originally Posted by apaininthebutt View Post
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.

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
Old 02-22-2009, 12:25 PM ph00ny is offline  
Reply With Quote
#2  

apaininthebutt
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...
Old 02-22-2009, 12:32 PM apaininthebutt is offline  
Reply With Quote
#3  

whiteboy
 
whiteboy's Avatar
 
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
Old 02-22-2009, 01:47 PM whiteboy is offline  
Reply With Quote
#4  

apaininthebutt
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
Old 02-22-2009, 04:34 PM apaininthebutt is offline  
Reply With Quote
#5  

whiteboy
 
whiteboy's Avatar
 
Quote:
Originally Posted by apaininthebutt View Post


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

make sure there's an = at the beginning of the formula
__________________
comets are like cats, they have tails, and they do what they want
Old 02-22-2009, 10:02 PM whiteboy is offline  
Reply With Quote
#6  

apaininthebutt
Master of my heart <3 DopeKitchen
 
found the problem:
Old 02-23-2009, 12:11 AM apaininthebutt is offline  
Reply With Quote
#7  

ph00ny
 
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
Old 02-24-2009, 08:13 PM ph00ny is offline  
Reply With Quote
#8  

ph00ny
 
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
Old 02-24-2009, 08:42 PM ph00ny is offline  
Reply With Quote
#9  

whiteboy
 
whiteboy's Avatar
 
Quote:
Originally Posted by ph00ny View Post
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))

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
Old 02-24-2009, 09:28 PM whiteboy is offline  
Reply With Quote
#10  

ph00ny
 
Quote:
Originally Posted by whiteboy View Post
seems to work too except for the last column, getting inconsistent results

it works assuming last number = two digits or less
__________________
Yao Ming of Asian Crew
Second [M]ember of the 6' Asian club
[M]ember of [M] Cadillac Club
04 White Diamond CTS
f17881c2d660477b9151fe21fb4fb04b
Old 02-24-2009, 09:33 PM ph00ny is offline  
Reply With Quote
#11  

Reply


Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump



All times are GMT -7. The time now is 09:57 AM.



Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.