[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

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 Ash-Covered Coin (Qty: 25)     124,500 Ryo (4980 each) Ash-Covered Coin (Qty: 5)     25,000 Ryo (5000 each) Ash-Covered 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, AceLast edited by Ace1312 on Thu May 13, 2010 9:52 am; edited 1 time in total
ken johnson
Super User

Joined: 23 Apr 2009
Posts: 1984
Location: Sydney, Australia

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

Try these...
If Ash-Covered Coin (Qty: 25) is in A1 then...
 Code: =TRIM(LEFT(A1;FIND("(";A1)-1))
returns "Ash-Covered 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).
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.) Ash-Covered Coin (Qty: 25)     124,500 Ryo (4980 each) Ash-Covered Coin (Qty: 5)     25,000 Ryo (5000 each) Ash-Covered 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.
ken johnson
Super User

Joined: 23 Apr 2009
Posts: 1984
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).
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?
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.
ken johnson
Super User

Joined: 23 Apr 2009
Posts: 1984
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 non-breaking 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 non-breaking 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);"")));")";" ")))

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).
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum