+ Reply to Thread
Results 1 to 13 of 13

Concatenate non-adjacent cells

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Concatenate non-adjacent cells

    Hello everyone
    I have found this sub for concatenating non-adjacent columns but can't use it
    Please Login or Register  to view this content.
    How to concatenate non-adjacent cells in the same row but different columns?
    Thanks advanced
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  2. #2
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: Concatenate non-adjacent cells

    What exactly are you looking to achieve with this code? are you able to attach a 'before & after' workbook?

  3. #3
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Concatenate non-adjacent cells

    I need to concatenate B1:E1 and G1:H1 and K1 and L1:N1 (in one row which is here for example row1)

  4. #4
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: Concatenate non-adjacent cells

    I'm not sure I follow your requirement. It sounds very basic.
    One can do that with a formula.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    For VBA, say you want to store this concatenated string in Column P for each row then:
    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Concatenate non-adjacent cells

    It gets a value error ..
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Concatenate non-adjacent cells

    Concatenate only handles single cells, not ranges, so you would need a custom function for that.

  7. #7
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Concatenate non-adjacent cells

    Yes I know Mr. Stephen and I found the UDF in the first post but don't know how to use it ..
    Generall I found another one
    Please Login or Register  to view this content.

  8. #8
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Concatenate non-adjacent cells

    You need to specify a delimiter too

    =CONCAT(",",B1:E1,G1:H1,K1:N1)

  9. #9
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Concatenate non-adjacent cells

    Thanks Mr.Stephen
    In fact my problem was not with the last UDF function in post #7 ..
    My problem was with the UDF function in Post # 1 and how to use it ...I still need to know how to use it (From Post #1)
    Thanks a lot for reply

  10. #10
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Concatenate non-adjacent cells

    You have to call it from another macro, e.g.
    Please Login or Register  to view this content.
    Works for me.

  11. #11
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: Concatenate non-adjacent cells

    Oops, my bad!
    Didn't know that CONCATENATE works only with individual cell references, and not ranges. As you have only a few cells to concatenate, try this:
    Please Login or Register  to view this content.
    Formula would be:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by jewelsharma; 01-26-2016 at 10:07 AM. Reason: VBA code editted

  12. #12
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Concatenate non-adjacent cells

    This was just a sample and the original file more ranges will be concatenated .. and some are negative I mean may be A1:C1 and I1:K1 and then D1:E1 ...
    Thanks fro help I think I will use the UDF in post #7

  13. #13
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Concatenate non-adjacent cells

    Quote Originally Posted by StephenR View Post
    You have to call it from another macro, e.g.
    Please Login or Register  to view this content.
    Works for me.
    Thanks a lot Mr. Stephen
    I tested it and it works fine
    Thank you very much
    Best Regards

+ 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. Concatenate adjacent cells based upon cell value
    By michael.reynolds1775 in forum Excel General
    Replies: 10
    Last Post: 11-05-2014, 09:41 PM
  2. How to concatenate adjacent cells based on values present in previous columns?
    By Palraj in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-30-2014, 01:01 AM
  3. Macro to concatenate cells adjacent to duplicates
    By tkellymc in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-16-2013, 07:38 PM
  4. Concatenate multiple cell values based on matching adjacent cells
    By mkrzy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-10-2013, 02:40 AM
  5. [SOLVED] Macro to concatenate cells adjacent to duplicates
    By 084851 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-28-2013, 09:18 AM
  6. Concatenate non-adjacent cells without duplicates
    By turningitred in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-18-2009, 11:08 AM
  7. [SOLVED] How to concatenate adjacent cells in a range without using &?
    By Ark in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-16-2005, 02:05 PM

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