View previous topic :: View next topic 
Author 
Message 
Ace1312 Newbie
Joined: 11 May 2010 Posts: 3

Posted: Tue May 11, 2010 2:55 pm Post subject: [SOLVED]IF/Find but only moving parts of the cell 


AshCovered Coin (Qty: 25) 124,500 Ryo (4980 each)
AshCovered Coin (Qty: 5) 25,000 Ryo (5000 each)
AshCovered Coin (Qty: 1) 100,000 Ryo
Billy Bucket (Qty: 26) 57,000 Ryo (2192 each)
I already know how to pull info from other sheets, and I plan on pulling this 2 column info into a 4 column set. but, I need the description of the item in one column (column A), just the qty (I.E. 25) in another one, and then just the first number from the second column into C (I.E. 124,500). It won't be hard to get the 4th number using simple math in the cell. I would also like to find a way to then compare column D but only between cells that column A is the same in, this will vary and so i won't know exactly how many times the description will be the same.
Any help would be awesome, I can play around a bit more once I get some ideas to work with.
Thanks for the help,
Regards,
Ace
Last edited by Ace1312 on Thu May 13, 2010 9:52 am; edited 1 time in total 

Back to top 


ken johnson Super User
Joined: 23 Apr 2009 Posts: 2032 Location: Sydney, Australia

Posted: Tue May 11, 2010 5:05 pm Post subject: 


Try these...
If AshCovered Coin (Qty: 25) is in A1 then...
Code:  =TRIM(LEFT(A1;FIND("(";A1)1))
 returns "AshCovered Coin" and...
Code:  =VALUE(SUBSTITUTE(TRIM(MID(A1;FIND(":";A1)+1;255));")";""))  returns 25.
If 124,500 Ryo (4980 each) is in F1 then...
Code:  =VALUE(TRIM(LEFT(E1;FIND("R";E1)1)))  returns 124500.
Ken Johnson _________________ If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). 

Back to top 


Ace1312 Newbie
Joined: 11 May 2010 Posts: 3

Posted: Tue May 11, 2010 6:18 pm Post subject: 


Thanks much two of the three worked great. I'm trying to look at the one that should be returning the qty number to figure out why it isn't working.
Also, let me rephrase the last part of my first post. I need to find a way to conditionally format the cells so that they will highlight the lowest number in column D but for each set of items (I.E. There are 3 different Ash covered coins, 4 different billy buckets, and 2 different boring bulbs, I need the lowest number of each highlighted.)
AshCovered Coin (Qty: 25) 124,500 Ryo (4980 each)
AshCovered Coin (Qty: 5) 25,000 Ryo (5000 each)
AshCovered Coin (Qty: 1) 100,000 Ryo
Billy Bucket (Qty: 13) 28,500 Ryo (2192 each)
Billy Bucket (Qty: 10) 21,500 Ryo (2150 each)
Billy Bucket (Qty: 10) 25,000 Ryo (2500 each)
Billy Bucket (Qty: 10) 21,500 Ryo (2150 each)
Boring Bulb (Qty: 26) 52,000 Ryo (2000 each)
Boring Bulb (Qty: 50) 24,000 Ryo (480 each)
Thanks again. 

Back to top 


ken johnson Super User
Joined: 23 Apr 2009 Posts: 2032 Location: Sydney, Australia

Posted: Tue May 11, 2010 10:37 pm Post subject: 


Here is a sample doc.
http://www.4shared.com/file/0_LP084i/Text_to_columns_simulation.html
I've had to use an array formula to compare each column D value with other column D values with the same column A value so that the appropriate minimum could be determined.
As far as I can tell Conditional Formatting does not recognise array formulas so the array formulas are in column E where either TRUE or FALSE is returned. Then the Conditional formatting simply refers to the value in column E. Column E can be hidden from view.
Ken Johnson _________________ If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). 

Back to top 


Ace1312 Newbie
Joined: 11 May 2010 Posts: 3

Posted: Wed May 12, 2010 7:24 am Post subject: 


ok, one last question, I figured out why I thought that that formula didn't work. Apparently all of my data has 4 spaces behind it. I've tried to find and replace that with nothing, but it won't let me search for spaces. is their a faster way to delete those 4 spaces? or am I going to have to go through manually to do that? 

Back to top 


RiderofDark Newbie
Joined: 12 May 2010 Posts: 1

Posted: Wed May 12, 2010 7:31 am Post subject: 


To help the OP (since I'm working alongside him on this) explain what's going on...
"Smoke Bombs (Qty: 19) " is an example of the exact text string that is getting looked at.
I use the formula =TRIM(SUBSTITUTE(RIGHT(G1;LEN(G1)FIND(":";G1));")";" ")) and I get a text value of 19. I put a VALUE() statement around that whole thing and I get an Err:502 instead of a result of 19 as an integer value. 

Back to top 


ken johnson Super User
Joined: 23 Apr 2009 Posts: 2032 Location: Sydney, Australia

Posted: Thu May 13, 2010 12:36 am Post subject: 


RiderofDark wrote:  To help the OP (since I'm working alongside him on this) explain what's going on...
"Smoke Bombs (Qty: 19) " is an example of the exact text string that is getting looked at.
I use the formula =TRIM(SUBSTITUTE(RIGHT(G1;LEN(G1)FIND(":";G1));")";" ")) and I get a text value of 19. I put a VALUE() statement around that whole thing and I get an Err:502 instead of a result of 19 as an integer value. 
One possibility is those trailling "invisible" characters are not spaces. They could be nonbreaking spaces (CHAR(160)). I tried Code:  ="Smoke Bombs (Qty: 19)"&REPT(CHAR(160);4)  in G1 and your formula inside the VALUE function resulted in Err:502.
One possible solution is to use the SUBSTITUTE function to get rid of the nonbreaking spaces...
Code:  =VALUE(SUBSTITUTE(TRIM(MID(SUBSTITUTE(Original_Data.A1;CHAR(160);"");FIND(":";Original_Data.A1)+1;255));")";""))  in my doc
Code:  =VALUE(TRIM(SUBSTITUTE(RIGHT(SUBSTITUTE(G1;CHAR(160);"");LEN(SUBSTITUTE(G1;CHAR(160);""))FIND(":";SUBSTITUTE(G1;CHAR(160);"")));")";" ")))  in your doc
Ken Johnson
EDIT:Replaced "CODE" with "CHAR" in last formula (What was I thinking!?)
EDIT2:Corrected error in penultimate formula where the last parenthesis was not included inside the code frame. _________________ If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). 

Back to top 


