 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / July 2007=Concatenate
Hi, I am using this formula: =(A1&", "&B1&", "&C1&", "&D1&", "&E1&", "&F1) Problem is, sometimes a few of the cells are blank. I end up getting 2 commas , , together. I would like to add a conditional that would check to see if the cell is blank. If so, skip the cell. How would I do that? Thank you. David Hi David, This doesn't check for blanks beforehand, it cleans them up afterwards. =SUBSTITUTE((A1&", "&B1&", "&C1&", "&D1&", "&E1&", "&F1)," ,","") HTH Martin > Hi, > [quoted text clipped - 10 lines] > Thank you. > David Not quite. Clear all the cells in the range. The formula returns a comma. Now, enter something in cell D1. The formula still returns an extra comma.  SignatureBiff Microsoft Excel MVP > Hi David, > [quoted text clipped - 20 lines] >> Thank you. >> David Yeah Biff, it falls down in a couple of places. When A1 is blank (very untidy) When F1 is blank (a little untidy) I'm glad you came in when you did, I was just starting to explore a way to trim it but I was starting in the wrong direction, who knows where I would have ended up. Regards Martin > Not quite. > [quoted text clipped - 25 lines] >>> Thank you. >>> David Try this: =SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1&" "&F1)," ",", ") And, just in case it gets chopped up by line wrap and removes the spaces: =SUBSTITUTE(TRIM(A1&" "&B1&" "&C1 &" "&D1&" "&E1&" "&F1)," ",", ") SignatureBiff Microsoft Excel MVP > Hi, > [quoted text clipped - 10 lines] > Thank you. > David I like this technique--but only if each of the cells does not have any spaces in it. (Just a small warning to the OP.) > Try this: > [quoted text clipped - 23 lines] > > Thank you. > > David SignatureDave Peterson Good catch. I didn't even think of spaces within the cells and so far I'm gettin' nowhere trying to fix that. SignatureBiff Microsoft Excel MVP >I like this technique--but only if each of the cells does not have any >spaces in [quoted text clipped - 29 lines] >> > Thank you. >> > David You could use substitute() a bunch of times. Not too bad with 6 cells, but sooner or later, I'd break down and use a UDF like the one at JE McGimpsey's site: http://www.mcgimpsey.com/excel/udfs/multicat.html > Good catch. I didn't even think of spaces within the cells and so far I'm > gettin' nowhere trying to fix that. [quoted text clipped - 40 lines] > > > > Dave Peterson SignatureDave Peterson > Good catch. I didn't even think of spaces within the cells and so far I'm > gettin' nowhere trying to fix that. This seems to work.... =SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1&","&B1&","&C1&","&D1&","&E1&","&F1," ","|"),","," "))," ",","),"|"," ") Rick And now try it with 30 columns <bg> Peo >> Good catch. I didn't even think of spaces within the cells and so far I'm >> gettin' nowhere trying to fix that. [quoted text clipped - 5 lines] > > Rick Thankfully, that wasn't the question that was asked (it was hard enough to get this one to work).<vbg> Rick > And now try it with 30 columns <bg> > [quoted text clipped - 9 lines] >> >> Rick Oh, what the hell... it is only a little more typing... =SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1&","&B1&","&C1&","&D1&","&E1&","&F1&","&G1&","&H1&","&I1&","&J1&","&K1&","&L1&","&M1&","&N1&","&O1&","&P1&","&Q1&","&R1&","&S1&","&T1&","&U1&","&V1&","&W1&","&X1&","&Y1&","&Z1&","&AA1&","&AB1&","&AC1&","&AD1," ","|"),","," "))," ",","),"|"," ") <g> Rick > Thankfully, that wasn't the question that was asked (it was hard enough to > get this one to work).<vbg> [quoted text clipped - 14 lines] >>> >>> Rick Not sure why the line got broken up with the blank line between it (it also broke at a blank space making it hard to see. Here is the "line", broken up in more manageable lengths. You will have to manually concatenate it in something like notepad before attempting to paste it into the worksheet... =SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE (A1&","&B1&","&C1&","&D1&","&E1&","&F1&","&G1&"," &H1&","&I1&","&J1&","&K1&","&L1&","&M1&","&N1&","& O1&","&P1&","&Q1&","&R1&","&S1&","&T1&","&U1&","& V1&","&W1&","&X1&","&Y1&","&Z1&","&AA1&","&AB1&","& AC1&","&AD1," ","|"),","," "))," ",","),"|"," ") Rick > Oh, what the hell... it is only a little more typing... > [quoted text clipped - 23 lines] >>>> >>>> Rick No need for notepad, just paste into the formula bar one line at a time. Regards Martin > Not sure why the line got broken up with the blank line between it (it > also broke at a blank space making it hard to see. Here is the "line", [quoted text clipped - 38 lines] >>>>> >>>>> Rick Or just paste it all into the formula bar and then delete the line breaks. SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > No need for notepad, just paste into > the formula bar one line at a time. [quoted text clipped - 44 lines] >>>>>> >>>>>> Rick > Or just paste it all into the formula bar and then delete the line breaks. Ah, but of course... paste it into the FORMULA BAR, not the cell itself <imagine the sound of a hand slapping one's forehead here>. Rick >You will have to manually concatenate it in something like notepad Select the cell where you want to paste the formula. Then paste the formula in the *formula bar*. You can then backspace out the line breaks. SignatureBiff Microsoft Excel MVP > Not sure why the line got broken up with the blank line between it (it > also broke at a blank space making it hard to see. Here is the "line", [quoted text clipped - 38 lines] >>>>> >>>>> Rick Where's the space after each item? <g> xx, aa, yy, zz SignatureBiff Microsoft Excel MVP >> Good catch. I didn't even think of spaces within the cells and so far I'm >> gettin' nowhere trying to fix that. [quoted text clipped - 5 lines] > > Rick >>> Good catch. I didn't even think of spaces within the cells and so far >>> I'm gettin' nowhere trying to fix that. [quoted text clipped - 7 lines] > > xx, aa, yy, zz I didn't think the OP really wanted them.<g> Actually, those damned space kept getting in the way of everything I tried. Finally, I got the bright idea to dump them, which ended up making the parsing easier... then I forgot to put them back in. I just needed to add one space character into the formula to put them back... =SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1&","&B1&","&C1&","&D1&","&E1&","&F1,"","|"),","," "))," ",", "),"|"," ") Rick >>>> Good catch. I didn't even think of spaces within the cells and so far >>>> I'm gettin' nowhere trying to fix that. [quoted text clipped - 19 lines] > > Rick Not there yet. A1 = Let's see Result = Lets, see SignatureBiff Microsoft Excel MVP >> =SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1&","&B1&","&C1&","&D1&","&E1&","&F1,"","|"),","," >> "))," ",", "),"|"," ") [quoted text clipped - 4 lines] > > Result = Lets, see ???? I just retested it to be sure... that's not what happens on my system. Besides, I'm not even touching the apostrophe character in my manipulations, so I don't see how the result you posted could happen. Rick >>> =SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1&","&B1&","&C1&","&D1&","&E1&","&F1,"","|"),","," >>> "))," ",", "),"|"," ") [quoted text clipped - 12 lines] > > Rick http://img110.imageshack.us/img110/5131/concatcellsrx3.jpg Hard to tell at the end of the formula what are spaces. I just pasted the formula into the formula bar and backspaced out the line breaks. SignatureBiff Microsoft Excel MVP >>>> =SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1&","&B1&","&C1&","&D1&","&E1&","&F1,"","|"),","," >>>> "))," ",", "),"|"," ") [quoted text clipped - 17 lines] > Hard to tell at the end of the formula what are spaces. I just pasted the > formula into the formula bar and backspaced out the line breaks. Which is what I did to retest it. Since your posted sample shows the apostrophe in the word Let's, I'm guessing you got my formula to work now, right? Rick >>>>> =SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1&","&B1&","&C1&","&D1&","&E1&","&F1,"","|"),","," >>>>> "))," ",", "),"|"," ") [quoted text clipped - 23 lines] > > Rick I must be missing something here. The screencap shows the result of the formula as: Let's, see The comma is not supposed to be there. What does the apostrophe have to do with anything? I'm confused! SignatureBiff Microsoft Excel MVP > The comma is not supposed to be there. > > What does the apostrophe have to do with anything? > > I'm confused! Your first post said this... A1 = Let's see Result = Lets, see You left out the apostrophe on your Result line and so I ended up concentrating on that omission instead of what you were actually saying. I guess it is back to the drawing boards on my formula. <g> Rick >> The comma is not supposed to be there. >> [quoted text clipped - 14 lines] > > Rick >>> Your first post said this... >A1 = Let's see >Result = Lets, see Oh, I see! <g> So I'm the source of the confusion. Well, it's not the first time that's happened! Good luck on figuring this out (without ending up with a monster formula). SignatureBiff Microsoft Excel MVP > Good luck on figuring this out One of my space characters got lost somewhere... I put it back and I think the formula work as it was (with that one space character added back in). > (without ending up with a monster formula). I don't think so. As I said, I believe my original formula (plus the "lost space") works. Here is the file purposely broken up to avoid losing any spaces again... =SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE (A1&","&B1&","&C1&","&D1&","&E1&","&F1," ","|"),","," ")) ," ",", "),"|"," ") Rick >> Good luck on figuring this out > [quoted text clipped - 12 lines] > > Rick Ok, nice goin'! I finally got it to work. One thing I did notice is that if a cell contains something like: 10, 10 then it adds another space: 10, 10. But, if the cells do contain commas then it would make sense to use a different delimiter. For example: A1 = 10, 10 B1 = 10 Result = 10, 10, 10 you could TRIM out the extra space but it's impossible to distinguish if the result came from 1, 2 or 3 cells. 10, 10, 10 So in this case a different delimiter should be used. Let's put this puppy to rest! SignatureBiff Microsoft Excel MVP "T. Valko" <biffinp...@comcast.net> wrote... ... >I finally got it to work. > [quoted text clipped - 21 lines] > >Let's put this puppy to rest! ... Oh, ye of little endurance! Hundreds of messages have been written on this topic in many different programming language ngs. If the delimiting char could appear in a field, then an alternative would be delimiting the field, say, with double quotes. At that point it becomes expedient to process each field before concatenating it to the others. As in this monster. =SUBSTITUTE(SUBSTITUTE(TRIM( SUBSTITUTE(IF(COUNTIF(A1,"*,*")-COUNTIF(A1,"""*"""), """"&A1&"""",A1)," ",CHAR(127))&" "& SUBSTITUTE(IF(COUNTIF(B1,"*,*")-COUNTIF(B1,"""*"""), """"&B1&"""",B1)," ",CHAR(127))&" "& SUBSTITUTE(IF(COUNTIF(C1,"*,*")-COUNTIF(C1,"""*"""), """"&C1&"""",C1)," ",CHAR(127))&" "& SUBSTITUTE(IF(COUNTIF(D1,"*,*")-COUNTIF(D1,"""*"""), """"&D1&"""",D1)," ",CHAR(127))&" "& SUBSTITUTE(IF(COUNTIF(E1,"*,*")-COUNTIF(E1,"""*"""), """"&E1&"""",E1)," ",CHAR(127)))," ",", "),CHAR(127)," ") > Oh, ye of little endurance! Hundreds of messages have been written on > this topic in many different programming language ngs. [quoted text clipped - 15 lines] > SUBSTITUTE(IF(COUNTIF(E1,"*,*")-COUNTIF(E1,"""*"""), > """"&E1&"""",E1)," ",CHAR(127)))," ",", "),CHAR(127)," ") Two things about your formula. One, you stopped one column too soon... the OP wanted it carried out to Column F. Not a big deal as the extension is easy enough to do. Two, why are you subtracting off COUNTIF(C1,"""*""") in your IF conditional tests? I mean, I think I know what you were trying to do (stop some kind of recursion); but, on a hunch, I removed them all and your formula worked fine without them. The only other thing you could have done to shorten you formula is to use CHAR(1) instead of CHAR(127) which saves 14 (assuming you extend your formula for Column F. Here is your formula reworked for the above comments... =SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(IF(COUNTIF(A1,"*,*"),""""&A1&"""",A1)," ",CHAR(1))&" "&SUBSTITUTE(IF(COUNTIF(B1,"*,*"),""""&B1&"""",B1)," ",CHAR(1))&" "&SUBSTITUTE(IF(COUNTIF(C1,"*,*"),""""&C1&"""",C1)," ",CHAR(1))&" "&SUBSTITUTE(IF(COUNTIF(D1,"*,*"),""""&D1&"""",D1)," ",CHAR(1))&" "&SUBSTITUTE(IF(COUNTIF(E1,"*,*"),""""&E1&"""",E1)," ",CHAR(1))&" "&SUBSTITUTE(IF(COUNTIF(F1,"*,*"),""""&F1&"""",F1)," ",CHAR(1)))," ",", "),CHAR(1)," ") As far as I can tell, it seems to work fine. Rick >> Oh, ye of little endurance! Hundreds of messages have been written on >> this topic in many different programming language ngs. [quoted text clipped - 37 lines] > > Rick I was thinking more along the lines of: =SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE( A1&"-"&B1&"-"&C1&"-"&D1&"-"&E1&"-"&F1 ," ","|"),"-"," "))," "," - "),"|"," ") Where: A1 = 10, 10 B1 = 10 Then: 10, 10 - 10 Just substitute the "-" for the between cell delimiter. Although I have to admit that using a dash isn't really visually appealing to me but neither are the quotes. >Hundreds of messages have been written on >this topic in many different programming language ngs. I'm sure there have been. This is similar to parsing names/addresses. SignatureBiff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" <rickNOSPAMnews@NOSPAMcomcast.net> wrote... ... > . . . Two, why are you subtracting off COUNTIF(C1,"""*""") in your IF > conditional tests? . . . ... On the assumption that if the field is already double-quote delimited, it doesn't need bracketing double quotes, but I screwed up the test. My original test would have added unnecessary bracketing double quotes if the field were already bracketed by double quotes but contained no commas. Should have used COUNTIF(A1,"*,*")*COUNTIF(A1,"<>""*""") instead. Hello David, Why not just array-enter =MultiCat(ReturnNonEmpty(A1:F1),",") ? See http://www.sulprobil.com/html/concatenate.html please. Regards, Bernd |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.