Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I ignore blanks when concatenating cells in Excel?
Hi there,
I have run out of ideas on this one and now I've lost my fresh morning mind, so any help would be much appreciated. I have a list of garments and each style has a different number of sizes in its size range from a minimum of 1 available size to a maximum of 24 available sizes, so I have 24 columns for sizes. I want to concatenate the available sizes for each style separated by a comma and a space. A simplified version of the data as it currently stands is like this: A B C D E etc.....out to 24 columns 1 Stock Item Name Available Sizes 2 Stock Item A 8 10 12 14 3 Stock Item B S M L 4 Stock Item C OS and the result I want in one column (single cell) is: A B 1 Stock Item Name Available Sizes 2 Stock Item A 8, 10, 12, 14 3 Stock Item B S, M, L 4 Stock Item C OS The problem when I use concatenate or "&" across the 24 columns is that I end up with commas and spaces after the available sizes for all of the remaining blank cells. I can't use IF because I can't embed that many IF statements in the one formula. Hope this makes sense. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I ignore blanks when concatenating cells in Excel?
As long as there are no embedded spaces in any of those columns (C:Z), you could
use: =substitute(trim(c2&" "&d2&" "&e2&" "...&" "&z2)," ",", ")) Or you could use/modify JE McGimpsey's code: http://mcgimpsey.com/excel/udfs/multicat.html Modified: Public Function MultiCat( _ ByRef rRng As Excel.Range, _ Optional ByVal sDelim As String = "") _ As String Dim rCell As Range For Each rCell In rRng if rcell.value = "" then 'do nothing else MultiCat = MultiCat & sDelim & rCell.Text end if Next rCell if len(multicat) 0 then MultiCat = Mid(MultiCat, Len(sDelim) + 1) end if End Function And use it in a cell like: =multicat(c2:z2,", ") JE has instructions on how to install it at his site, too. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm NatChat wrote: Hi there, I have run out of ideas on this one and now I've lost my fresh morning mind, so any help would be much appreciated. I have a list of garments and each style has a different number of sizes in its size range from a minimum of 1 available size to a maximum of 24 available sizes, so I have 24 columns for sizes. I want to concatenate the available sizes for each style separated by a comma and a space. A simplified version of the data as it currently stands is like this: A B C D E etc.....out to 24 columns 1 Stock Item Name Available Sizes 2 Stock Item A 8 10 12 14 3 Stock Item B S M L 4 Stock Item C OS and the result I want in one column (single cell) is: A B 1 Stock Item Name Available Sizes 2 Stock Item A 8, 10, 12, 14 3 Stock Item B S, M, L 4 Stock Item C OS The problem when I use concatenate or "&" across the 24 columns is that I end up with commas and spaces after the available sizes for all of the remaining blank cells. I can't use IF because I can't embed that many IF statements in the one formula. Hope this makes sense. Thanks. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I ignore blanks when concatenating cells in Excel?
One way is use TRIM on the individual cell concats - TRIM will remove all
extraneous white spaces except the single space in-between values, then use SUBSTITUTE to replace these single spaces: " " with a comma-space, ie: ", " Try this .. Insert a new col B, so your 24 cols would now be cols C to Z Then place in B2 something like this (assuming concats only for the 1st 4 cols: C to F): =SUBSTITUTE(TRIM(C2&" "&D2&" "&E2&" "&F2)," ",", ") Copy B2 down to return the desired neat results Extend the part within the TRIM(...) to suit Your final expression in B2 should look something like this: =SUBSTITUTE(TRIM(C2&" "&D2&" "&E2&" "&F2& ... &" "&Z2)," ",", ") Note that it's easier & neater to use the ampersand operator: & for the cell concats, instead of CONCATENATE -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "NatChat" wrote: Hi there, I have run out of ideas on this one and now I've lost my fresh morning mind, so any help would be much appreciated. I have a list of garments and each style has a different number of sizes in its size range from a minimum of 1 available size to a maximum of 24 available sizes, so I have 24 columns for sizes. I want to concatenate the available sizes for each style separated by a comma and a space. A simplified version of the data as it currently stands is like this: A B C D E etc.....out to 24 columns 1 Stock Item Name Available Sizes 2 Stock Item A 8 10 12 14 3 Stock Item B S M L 4 Stock Item C OS and the result I want in one column (single cell) is: A B 1 Stock Item Name Available Sizes 2 Stock Item A 8, 10, 12, 14 3 Stock Item B S, M, L 4 Stock Item C OS The problem when I use concatenate or "&" across the 24 columns is that I end up with commas and spaces after the available sizes for all of the remaining blank cells. I can't use IF because I can't embed that many IF statements in the one formula. Hope this makes sense. Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I ignore blanks when concatenating cells in Excel?
Thanks so much, your first suggestion worked perfectly!
"NatChat" wrote: Hi there, I have run out of ideas on this one and now I've lost my fresh morning mind, so any help would be much appreciated. I have a list of garments and each style has a different number of sizes in its size range from a minimum of 1 available size to a maximum of 24 available sizes, so I have 24 columns for sizes. I want to concatenate the available sizes for each style separated by a comma and a space. A simplified version of the data as it currently stands is like this: A B C D E etc.....out to 24 columns 1 Stock Item Name Available Sizes 2 Stock Item A 8 10 12 14 3 Stock Item B S M L 4 Stock Item C OS and the result I want in one column (single cell) is: A B 1 Stock Item Name Available Sizes 2 Stock Item A 8, 10, 12, 14 3 Stock Item B S, M, L 4 Stock Item C OS The problem when I use concatenate or "&" across the 24 columns is that I end up with commas and spaces after the available sizes for all of the remaining blank cells. I can't use IF because I can't embed that many IF statements in the one formula. Hope this makes sense. Thanks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I ignore blanks when concatenating cells in Excel?
Thanks so much for your help. I wasn't aware of SUBSTITUTE and TRIM before.
"Max" wrote: One way is use TRIM on the individual cell concats - TRIM will remove all extraneous white spaces except the single space in-between values, then use SUBSTITUTE to replace these single spaces: " " with a comma-space, ie: ", " Try this .. Insert a new col B, so your 24 cols would now be cols C to Z Then place in B2 something like this (assuming concats only for the 1st 4 cols: C to F): =SUBSTITUTE(TRIM(C2&" "&D2&" "&E2&" "&F2)," ",", ") Copy B2 down to return the desired neat results Extend the part within the TRIM(...) to suit Your final expression in B2 should look something like this: =SUBSTITUTE(TRIM(C2&" "&D2&" "&E2&" "&F2& ... &" "&Z2)," ",", ") Note that it's easier & neater to use the ampersand operator: & for the cell concats, instead of CONCATENATE -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "NatChat" wrote: Hi there, I have run out of ideas on this one and now I've lost my fresh morning mind, so any help would be much appreciated. I have a list of garments and each style has a different number of sizes in its size range from a minimum of 1 available size to a maximum of 24 available sizes, so I have 24 columns for sizes. I want to concatenate the available sizes for each style separated by a comma and a space. A simplified version of the data as it currently stands is like this: A B C D E etc.....out to 24 columns 1 Stock Item Name Available Sizes 2 Stock Item A 8 10 12 14 3 Stock Item B S M L 4 Stock Item C OS and the result I want in one column (single cell) is: A B 1 Stock Item Name Available Sizes 2 Stock Item A 8, 10, 12, 14 3 Stock Item B S, M, L 4 Stock Item C OS The problem when I use concatenate or "&" across the 24 columns is that I end up with commas and spaces after the available sizes for all of the remaining blank cells. I can't use IF because I can't embed that many IF statements in the one formula. Hope this makes sense. Thanks. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I ignore blanks when concatenating cells in Excel?
Welcome, NatChat ..
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- On Feb 26, 12:06 pm, NatChat wrote: Thanks so much for your help. I wasn't aware of SUBSTITUTE and TRIM before. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy range of cells with blanks then paste without blanks | Excel Worksheet Functions | |||
Data Validation - ignore blanks | Excel Discussion (Misc queries) | |||
How do I ignore blanks within my list without losing references? | Excel Discussion (Misc queries) | |||
Ignore Blanks in Data Validation | Excel Worksheet Functions | |||
USING IGNORE BLANKS IN FORMULA | Excel Discussion (Misc queries) |