+ Reply to Thread
Results 1 to 14 of 14

tricks for using concatenate

  1. #1
    Registered User
    Join Date
    05-23-2020
    Location
    USA
    MS-Off Ver
    2007 Microsoft Office
    Posts
    14

    tricks for using concatenate

    I'm relatively new to concatenating in Excel (Office 2007) but have learned to do it.

    In a very large spreadsheet with multiple columns and over 200,000 rows I need to concatenate repeatedly as I prepare the data to be moved to Word. I have 2 types of concatenate needs:

    Type 1
    Within the spreadsheet, I sometimes need to concatenate 2 cells. Other times 3, 4, or even 5+. Usually contingent cells in rows below.
    For the many instances I need to concatenate 2 cells I learned to just copy that cell to the new location and the formula works perfectly for that cell too because the cells included change appropriately.
    But when I need to concatenate 3 or 5 cells I need to recreate the concatenate folmula which I find time consuming and error prone.

    Question: is there a trick to tell the formula "this time I want the next 2 cells. This time the next 3. Etc."? Dragging does not work for me because I want to add a space or a character between each added cell and the unmodified concatenate formula does not do this without modification.

    Maybe a series of macros? (This is a large dataset......)

    Type 2
    Is it possible to concatenate cells from Excel spreadsheet A directly into a cell in Excel spreadsheet B?
    I can obviously do the concatenation in one spreadsheet and copy the results into the other, but that is time consuming and error prone. Can it be done "directly"?

    How?

    Thanks you.

  2. #2
    Valued Forum Contributor
    Join Date
    06-22-2018
    Location
    Blackpool, England
    MS-Off Ver
    2019
    Posts
    375

    Re: tricks for using concatenate

    Perhaps you can upload a desensitized sample of what you've got and what you're trying to acchieve? Then we can look at the layout and make suggestions.

    See the yellow band at the top of the page for how!

    Tim
    Never stop learning!
    <--- please consider *-ing !

  3. #3
    Registered User
    Join Date
    05-23-2020
    Location
    USA
    MS-Off Ver
    2007 Microsoft Office
    Posts
    14

    Re: tricks for using concatenate

    Thank you for taking the time to answer.
    I hope this explains things better.
    Attached Files Attached Files

  4. #4
    Forum Expert Sambo kid's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,674

    Re: tricks for using concatenate

    both your sheets are a bit confusing.
    1) your example workbook has data in all the cells going down col B. Based on your results workbook are you expecting the results of the concatenation formulas to override what is in those cells? If so that would need VBA.
    2) based on your results it is difficult to tell when you expect two values to be concatenated or three values to be concatenated. How would excel know when or where to make those concatenations?
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  5. #5
    Valued Forum Contributor
    Join Date
    06-22-2018
    Location
    Blackpool, England
    MS-Off Ver
    2019
    Posts
    375

    Re: tricks for using concatenate

    Had a look at your sample file. In addition to what Sam has mentioned, I'm also confused because you say you're using a formula, so a few follow-up questions:

    1. What are you currently using, and where does that formula get it's information from.
    2. Is this a one-off migration, or is it going to be a regular occurrence?
      If the former, a bit of manual labour might just have to do, if it's the latter it might be worth investing the time to put the data in a more friendly format
    3. Where do the data come from - do you have control over layout etc?
    4. Would it be impractical to re-arrange the data?
    5. What tells you the number of cells that need to be concatenated?
      E.g, are we looking at team leaders who are responsible for others' work, or something, Is there a list of who's responsible for whom?
      etc...

    An in-cell formula can only concatenate values from other cells, it can't add to what's already in that cell, but you could use a helper column. That might be an option if it's a one-off... use TEXTJOIN in a helper column, then copy and paste the values back into the original and delete the helper column. This might be feasible if it's a one-off migration.

    On the other hand, you could create (on a separate worksheet, maybe) a table that holds the concatenation info, e.g.
    Cell: Workbook: concatenate cells:
    B2 This B2:B3
    B17 DifferentWorkbook G546:G548
    Then run a VBA routine that reads that info and writes directly into the target cells. It might be worth this whether it's a one-off or a regular thing. It'd depend on the effort: is it more, or less effort than using a helper column or manually doing the work?

    Whichever path you eventually go, unless there's a pattern to the concatenations or at least a summary somewhere it's going to be long haul with that many rows.

    Sorry.

    Tim
    Last edited by harrisonland; 05-24-2020 at 04:48 AM.

  6. #6
    Registered User
    Join Date
    05-23-2020
    Location
    USA
    MS-Off Ver
    2007 Microsoft Office
    Posts
    14

    Re: tricks for using concatenate

    Thank you all.

    I really need to learn more about Excel. With all the libraries closed because of Covid I will just need to wait till they reopen and I can get out books to learn Excel beyond the basics.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    37,691

    Re: tricks for using concatenate

    The Internet isn't closed, nor is this forum (I've learned what I know from a combination of the two). I would advise that you update your Excel version, though, as you are missing out on a lot of new functionality.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  8. #8
    Registered User
    Join Date
    05-23-2020
    Location
    USA
    MS-Off Ver
    2007 Microsoft Office
    Posts
    14

    Re: tricks for using concatenate

    You are correct of course. The internet is a wonderful resource. But it contains a lot of contradictory or out of date information that can lead one astray. As an octagenerian I still find books more structured and easier for me to learn from,
    Problem is not yet really solved. Just postponed for now. My fault entirely for not properly explaining what I really mean.

  9. #9
    Forum Expert Sambo kid's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,674

    Re: tricks for using concatenate

    If you'd answer some of the questions posed we might be able to solve it for you, or at least get things going in the right direction.
    for example, in your example workbook you have Data I and text B2 and text B3 in the cells in B1, B2 and B3 then in your results workbook you have text B2, text B3 across from Jerry but not across from John. You repeat this in a similar way for row 6 of your results workbook for Bennett.
    The problem is that Excel needs to know when to break things apart OR put things together. So under what circumstances (things that excel can "look" for when using a formula) does the text get concatenated for Jerry and Bennett but not John or Andrew etc.? And if you are open to using a helper column a formula can work. For example column E is empty so a formula could be there to do the concatenations for you and then it could be copied and pasted over column B.

    But if you want the text in column B to be overwritten as I noted in post #4 you will need VBA which is a whole different issue.
    Last edited by Sambo kid; 05-25-2020 at 09:42 AM.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    37,691

    Re: tricks for using concatenate

    In terms of searching the Internet, you can avoid out-of-date articles by narrowing your search to recent pages. I suggest you bookmark and use this page:

    https://www.google.co.uk/advanced_search

    Books are almost always completely out-of-date the minute you buy them.

  11. #11
    Registered User
    Join Date
    05-23-2020
    Location
    USA
    MS-Off Ver
    2007 Microsoft Office
    Posts
    14

    Re: tricks for using concatenate

    It is difficult for me to explain and we seem to be going into wrong directions because I am explaining poorly. I will try again, perhaps explain better. But I don't know all the correct terms to explain properly.

    I am not looking at automating the entire process. Just the creation of various types of the =concatenate....... types.

    Step 1:
    Say I wish to concatenate 3 cells into an existing cell overwriting that recipient cell's data. The way I know to do this is enter that recipient cell and type "=concatenate......".

    The ..... part after the word concatenate is difficult for me. I know you can drag the mouse and these cells get inculded in the "=concatenate......" formula that appears in the cell. But, I want to have a comma or slash between the various cells whose data is being concatenated. The added character is not added in the method I just described. The only way I know to add this in-between character is to edit the default entry Excel provides when I drag the cells I want to add.

    OK, I can do this manually but I discovered I often get it wrong and it takes several revisions till I get it correct. Is thre a way to tell Excel to include, say a / and/or space. by default, between the concatenated data items without needing to manually edit the formula it enters in th cell?

    If not, that's life. I can live with that. The problem is I need to repeat this process multiple times at various places in this large database. I don't expect Excel to know where these locations are. I will do select them manually. What I would like is a quick way to repeat the formula in step 1 (including the / or ,).

    I discovered that one way is to copy the concatenated cell and paste it elsewhere. But, sometimes I wish to concatenate different numbers of cells. Other times 5 cells. So I need to modify the pasted cell which, for me, is very error prone and time consuming.

    So I need to a simple way to tell Excel "I want to concatenate cells X25, X27, and X33 in the cell B14 overwriting its current data, with this character between the data items". Is that possible?
    Last edited by Andrews493; 05-25-2020 at 09:48 AM.

  12. #12
    Forum Expert Sambo kid's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,674

    Re: tricks for using concatenate

    well, for your part "Say I wish to concatenate 3 cells into an existing cell overwriting that recipient cell's data." that will be VBA.

    But, for simple formulas to concatenate you can use =CONCATENATE( and what you want to concatenate ) but I find easiest way is to do this...
    =B2&", "&B3&", "B4 as often as you want.
    that will take the text in B2 and put a comma and space after it then put the text in the cell from B3 and comma and space then the text from B4.

    Hope that helps you - but again, that would have to be in a different column, not the column with the data. But as I also noted, once that was accomplished in an empty cell, you could always copy the row, then using paste special >> values... paste the values over the information in column B.

  13. #13
    Registered User
    Join Date
    05-23-2020
    Location
    USA
    MS-Off Ver
    2007 Microsoft Office
    Posts
    14

    Re: tricks for using concatenate

    That is precisely what I was looking for! Thank you for understanding my badly put question.

    So just putting ", " between the cell addresses (without putting quotes or spaces around the cell addresses) adds the , and space.
    That is simple. Using the same example, will typing
    =B2"/ "B7"/ "B8 similarly put a / and a space between the various added data elements?

    And I assume you are saying that if I am in the B2 cell and it already contains data when I type in that string , that would not work because it automatically deletes the existing data. So I need to use an other cell and then copy the result to B2.
    Can the copying be automated as part of a macro?

    Can B2, B7, and B8 in that example be from a different Excel spreadsheet?

    Thank you so much. You answered my question.

    You see, I am truly a novice.......
    Last edited by AliGW; 05-25-2020 at 04:15 PM. Reason: Please donít quote unnecessarily!

  14. #14
    Forum Expert Sambo kid's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,674

    Re: tricks for using concatenate

    to address post #13, this =B2"/ "B7"/ "B8 I don't believe will work but this =B2&"/ "&B7&"/ "&B8 should work fine. You need the ampersands.

    If, for example, you had something in one cell in column A that you could point to to do the formula you could use an IF/THEN statement like...
    =IF(A2="something",B2&", "&B3,"") that says that if A2 is equal to something or has something in it then you concatenate what is in B2 with a comma and space and join it to B3, otherwise leave the cell blank.

    Yes, you are correct that if something is in B2 but you put a formula there whatever was there will be gone.

    And finally, you can combine B2 and B7 and B8 from another spreadsheet. It works easiest when both sheets are open but you can have the second sheet closed but that works best incorporating INDIRECT with the formula especially if you want updates from one sheet to appear in the other without opening both.

    Oh, and THANK YOU for the rep!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1