If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Wouldn't know what to call it!
I have a "table" that I created. It's just a basic grid with items on the y
axis and items on the x axis (ex. types of fruit on the top, number of fruit in the basket down the side). I need to pull the numbers of fruit in each basket, without adding them up, and combine them into one line, separating each with a comma. (Ex. 3, 4, 5, 6, 7, 8,...). I was able to do that by using "&", however, if there is no fruit in the basket (0), how can I not have a comma with a blank space appear until I do have fruit again (ex. 2,3,4,15,16,17,18,19,25 instead of 2,3,4,,,,,,,,,,,,,15,16,17,,,,,,,,,18,,19,,,,,,,25 )? I have the possibility of 144 entries, but also the posibility of only 1 entry. I'm trying to stay away from an "IF" function because it may end up very involved. Any help is greatly appreciated. I'm sorry if I didn't explain it well!! |
#3
|
|||
|
|||
Wouldn't know what to call it!
You could use something like this
=IF(A1="","",A1&", ")&IF(A2="","",A2&", ")&IF(A3="","",A3&", ")&IF(A4="","",A4&", ")&IF(A5="","",A5&", ")&IF(A6="","",A6&", ")&A7 -- Regards, Peo Sjoblom "1Fish2Fish" wrote in message ... I have a "table" that I created. It's just a basic grid with items on the y axis and items on the x axis (ex. types of fruit on the top, number of fruit in the basket down the side). I need to pull the numbers of fruit in each basket, without adding them up, and combine them into one line, separating each with a comma. (Ex. 3, 4, 5, 6, 7, 8,...). I was able to do that by using "&", however, if there is no fruit in the basket (0), how can I not have a comma with a blank space appear until I do have fruit again (ex. 2,3,4,15,16,17,18,19,25 instead of 2,3,4,,,,,,,,,,,,,15,16,17,,,,,,,,,18,,19,,,,,,,25 )? I have the possibility of 144 entries, but also the posibility of only 1 entry. I'm trying to stay away from an "IF" function because it may end up very involved. Any help is greatly appreciated. I'm sorry if I didn't explain it well!! |
#4
|
|||
|
|||
Wouldn't know what to call it!
Don,
Thank you so much for responding. I'm not the best at VBA so I'm getting a runtime error of "invalid procedure." The line with the error is MsgBox Right(ms, Len(ms) - 1). Can you assist? "Don Guillett" wrote: Sub ifvaluestring() For Each c In Selection 'range("a2:a22") If Len(Application.Trim(c)) 0 And IsNumeric(c) Then ms = ms & "," & c Next MsgBox Right(ms, Len(ms) - 1) End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "1Fish2Fish" wrote in message ... I have a "table" that I created. It's just a basic grid with items on the y axis and items on the x axis (ex. types of fruit on the top, number of fruit in the basket down the side). I need to pull the numbers of fruit in each basket, without adding them up, and combine them into one line, separating each with a comma. (Ex. 3, 4, 5, 6, 7, 8,...). I was able to do that by using "&", however, if there is no fruit in the basket (0), how can I not have a comma with a blank space appear until I do have fruit again (ex. 2,3,4,15,16,17,18,19,25 instead of 2,3,4,,,,,,,,,,,,,15,16,17,,,,,,,,,18,,19,,,,,,,25 )? I have the possibility of 144 entries, but also the posibility of only 1 entry. I'm trying to stay away from an "IF" function because it may end up very involved. Any help is greatly appreciated. I'm sorry if I didn't explain it well!! |
#5
|
|||
|
|||
Wouldn't know what to call it!
Since I didn't know your range I just tested with a selected range of a mix
of numbers and text. Worked just fine. If all else fails, you may send your workbook to my address below along with snippets of these msgs. -- Don Guillett Microsoft MVP Excel SalesAid Software "1Fish2Fish" wrote in message news Don, Thank you so much for responding. I'm not the best at VBA so I'm getting a runtime error of "invalid procedure." The line with the error is MsgBox Right(ms, Len(ms) - 1). Can you assist? "Don Guillett" wrote: Sub ifvaluestring() For Each c In Selection 'range("a2:a22") If Len(Application.Trim(c)) 0 And IsNumeric(c) Then ms = ms & "," & c Next MsgBox Right(ms, Len(ms) - 1) End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "1Fish2Fish" wrote in message ... I have a "table" that I created. It's just a basic grid with items on the y axis and items on the x axis (ex. types of fruit on the top, number of fruit in the basket down the side). I need to pull the numbers of fruit in each basket, without adding them up, and combine them into one line, separating each with a comma. (Ex. 3, 4, 5, 6, 7, 8,...). I was able to do that by using "&", however, if there is no fruit in the basket (0), how can I not have a comma with a blank space appear until I do have fruit again (ex. 2,3,4,15,16,17,18,19,25 instead of 2,3,4,,,,,,,,,,,,,15,16,17,,,,,,,,,18,,19,,,,,,,25 )? I have the possibility of 144 entries, but also the posibility of only 1 entry. I'm trying to stay away from an "IF" function because it may end up very involved. Any help is greatly appreciated. I'm sorry if I didn't explain it well!! |
#6
|
|||
|
|||
Wouldn't know what to call it!
Either select a range first or use a range as Don indicated.
For Each c in Range("A2:A22") Gord Dibben MS Excel MVP On Tue, 26 Aug 2008 10:57:08 -0700, 1Fish2Fish wrote: Don, Thank you so much for responding. I'm not the best at VBA so I'm getting a runtime error of "invalid procedure." The line with the error is MsgBox Right(ms, Len(ms) - 1). Can you assist? "Don Guillett" wrote: Sub ifvaluestring() For Each c In Selection 'range("a2:a22") If Len(Application.Trim(c)) 0 And IsNumeric(c) Then ms = ms & "," & c Next MsgBox Right(ms, Len(ms) - 1) End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "1Fish2Fish" wrote in message ... I have a "table" that I created. It's just a basic grid with items on the y axis and items on the x axis (ex. types of fruit on the top, number of fruit in the basket down the side). I need to pull the numbers of fruit in each basket, without adding them up, and combine them into one line, separating each with a comma. (Ex. 3, 4, 5, 6, 7, 8,...). I was able to do that by using "&", however, if there is no fruit in the basket (0), how can I not have a comma with a blank space appear until I do have fruit again (ex. 2,3,4,15,16,17,18,19,25 instead of 2,3,4,,,,,,,,,,,,,15,16,17,,,,,,,,,18,,19,,,,,,,25 )? I have the possibility of 144 entries, but also the posibility of only 1 entry. I'm trying to stay away from an "IF" function because it may end up very involved. Any help is greatly appreciated. I'm sorry if I didn't explain it well!! |
Thread Tools | |
Display Modes | |
|
|