+ Reply to Thread
Results 1 to 13 of 13

VBA: Concatenate Strings in Adjacent Cells with a Space between them?

  1. #1
    Registered User
    Join Date
    02-18-2020
    Location
    London
    MS-Off Ver
    2021
    Posts
    77

    VBA: Concatenate Strings in Adjacent Cells with a Space between them?

    Hello

    I've been trying to write a macro to solve this problem all morning, so thought it was time to ask for help...

    I'd like to concatenate the text in 2 rows.

    Take the text in cells A1 to A100, add the text in adjacent cells B1 to B100 (adding a space between them) and output the result in the C column.

    The data in the B column sometimes ends in a space, so the output in the C column also needs to be trimmed to remove any trailing spaces.

    Any suggestions much appreciated, and I promise to mark this thread as solved when I find a solution


    Robin

  2. #2
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: VBA: Concatenate Strings in Adjacent Cells with a Space between them?

    No idea why this would be a macro, but ...
    Please Login or Register  to view this content.
    All the 'TRIMs' to get rid of any leading/trailing spaces plus also the intermediate space if Col A or Col B is blank

    (Typed freehand - testing was precisely zero)

  3. #3
    Registered User
    Join Date
    02-18-2020
    Location
    London
    MS-Off Ver
    2021
    Posts
    77

    Re: VBA: Concatenate Strings in Adjacent Cells with a Space between them?

    Thank you for your suggestion

    I am already using VBA to import data and therefore wanted a VBA solution rather than using an XLS formula.

    This code only works if the cells are adjacent, what would be a solution if the columns I'm adding are far apart please? (So I can specify the columns absolutely rather than using an offset)

    Also is there a solution without using a loop, since I have a lot of data and this will be quite slow.

    Thanks

  4. #4
    Forum Expert
    Join Date
    10-11-2021
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    1,500

    Re: VBA: Concatenate Strings in Adjacent Cells with a Space between them?

    At once

    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: VBA: Concatenate Strings in Adjacent Cells with a Space between them?

    This code only works if the cells are adjacent
    Yes, as mentioned in your original post...

    Take the text in cells A1 to A100, add the text in adjacent cells B1 to B100 (adding a space between them) and output the result in the C column.
    It is good to explain what you are looking for in the first post. Saves time
    Last edited by cytop; 05-09-2022 at 09:03 AM.

  6. #6
    Registered User
    Join Date
    02-18-2020
    Location
    London
    MS-Off Ver
    2021
    Posts
    77

    Re: VBA: Concatenate Strings in Adjacent Cells with a Space between them?

    Thank for this code, which I have tried and is very fast, but it puts the same result from the first row into every row?

  7. #7
    Registered User
    Join Date
    02-18-2020
    Location
    London
    MS-Off Ver
    2021
    Posts
    77

    Re: VBA: Concatenate Strings in Adjacent Cells with a Space between them?

    HTML Code: 
    Thanks to JEC this code quickly does what I want, but it puts the result from the first row into every subsequent row. How can we get this to move down one on each row please?

  8. #8
    Forum Expert
    Join Date
    10-11-2021
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    1,500

    Re: VBA: Concatenate Strings in Adjacent Cells with a Space between them?

    For me it's working as expected and how you say it should work.

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: VBA: Concatenate Strings in Adjacent Cells with a Space between them?

    Quote Originally Posted by vonsworld View Post

    Take the text in cells A1 to A100, add the text in adjacent cells B1 to B100 (adding a space between them) and output the result in the C column.

    The data in the B column sometimes ends in a space, so the output in the C column also needs to be trimmed to remove any trailing spaces.
    try
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    02-18-2020
    Location
    London
    MS-Off Ver
    2021
    Posts
    77

    Re: VBA: Concatenate Strings in Adjacent Cells with a Space between them?

    Maybe it's because I'm using Excel 2010? I'm attaching an XLS with your VBA and the duplicate results.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    02-18-2020
    Location
    London
    MS-Off Ver
    2021
    Posts
    77

    Re: VBA: Concatenate Strings in Adjacent Cells with a Space between them?

    Jindon, That works perfectly, Thank You!

  12. #12
    Forum Expert
    Join Date
    10-11-2021
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    1,500

    Re: VBA: Concatenate Strings in Adjacent Cells with a Space between them?

    For a dynamic range:

    Please Login or Register  to view this content.

  13. #13
    Valued Forum Contributor
    Join Date
    02-12-2011
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    831

    Re: VBA: Concatenate Strings in Adjacent Cells with a Space between them?

    Or:
    Please Login or Register  to view this content.
    Kind regards, Harry.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] shift+space only working on the last group of adjacent cells
    By hard_cell in forum Excel General
    Replies: 3
    Last Post: 07-03-2019, 02:53 AM
  2. [SOLVED] faster way to concatenate multiple cells with added space
    By seraphin in forum Excel General
    Replies: 2
    Last Post: 07-02-2019, 08:34 PM
  3. Concatenate non-adjacent cells
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 01-26-2016, 02:11 PM
  4. Replies: 2
    Last Post: 05-23-2014, 06:49 PM
  5. Replies: 8
    Last Post: 09-09-2013, 08:46 AM
  6. space between text strings with concatenate
    By Jeff in forum Excel General
    Replies: 2
    Last Post: 03-03-2005, 03:06 PM
  7. [SOLVED] Add Space between concatenate cells
    By Mac Landers in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-01-2005, 05:06 PM

Tags for this Thread

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