+ Reply to Thread
Results 1 to 21 of 21

VBA: Concatenate cell data one below another using the formula not working

  1. #1
    Registered User
    Join Date
    02-03-2021
    Location
    London
    MS-Off Ver
    2007
    Posts
    37

    VBA: Concatenate cell data one below another using the formula not working

    I have thousands of lines of text in cells of columns B and C in excel sheet1. There are empty cells in some places.
    I am trying to concatenate the text one below another using the formula =B2&CHAR(10)&C2 in the respective cells in D but the formula is not working.
    Please help.
    Last edited by zeo1; 07-10-2021 at 02:30 AM.

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,989

    Re: Concatenate cell data one below another using the formula not working

    Make sure that the cells in column D have "Wrap Text" turned on.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Registered User
    Join Date
    02-03-2021
    Location
    London
    MS-Off Ver
    2007
    Posts
    37

    Re: Concatenate cell data one below another using the formula not working

    No sir it is not working. Is there a VBA for it?

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,989

    Re: VBA: Concatenate cell data one below another using the formula not working

    Try:
    Please Login or Register  to view this content.
    Last edited by Mumps1; 07-10-2021 at 09:09 AM.

  5. #5
    Registered User
    Join Date
    02-03-2021
    Location
    London
    MS-Off Ver
    2007
    Posts
    37

    Re: VBA: Concatenate cell data one below another using the formula not working

    It is working but the VBA can not copy the complete data in the cells of D sir.

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,989

    Re: VBA: Concatenate cell data one below another using the formula not working

    I'm not sure what you mean. Please clarify in detail by
    cannot copy the complete data in the cells of D

  7. #7
    Registered User
    Join Date
    02-03-2021
    Location
    London
    MS-Off Ver
    2007
    Posts
    37

    Re: VBA: Concatenate cell data one below another using the formula not working

    Sir, I think the cell data limit is the issue.

    Sir, I copied the data in B and C from text files in a folder.
    Can we do it? If it makes sense
    1. If I can add the b text file data in the C text file (by giving the address of the files in C and D), then that will also work for me. or
    2. If I can add the b cell data in the C text file (by giving the address of the files in D) also will work for me.
    Last edited by zeo1; 07-13-2021 at 12:46 PM.

  8. #8
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,989

    Re: VBA: Concatenate cell data one below another using the formula not working

    What do you mean by the the cell data limit. Can you post a few examples of your data and explain how it is not working for you?

  9. #9
    Registered User
    Join Date
    02-03-2021
    Location
    London
    MS-Off Ver
    2007
    Posts
    37

    Re: VBA: Concatenate cell data one below another using the formula not working

    By cell data limit I mean the maximum number of characters an excel cell can accommodate. Is it clear sir?
    I am not sure about the issue just my blind guess.

  10. #10
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,989

    Re: VBA: Concatenate cell data one below another using the formula not working

    Are you saying that your cells contain more than 32,767 characters?

  11. #11
    Registered User
    Join Date
    02-03-2021
    Location
    London
    MS-Off Ver
    2007
    Posts
    37

    Re: VBA: Concatenate cell data one below another using the formula not working

    I think yes. Is there any way to check the number of characters in a cell?

  12. #12
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,989

    Re: VBA: Concatenate cell data one below another using the formula not working

    This formula will give you the number of characters in cell A1:

    =len(A1)

  13. #13
    Registered User
    Join Date
    02-03-2021
    Location
    London
    MS-Off Ver
    2007
    Posts
    37

    Re: VBA: Concatenate cell data one below another using the formula not working

    =LEN(D2) is showing 32767. Now ?

  14. #14
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,989

    Re: VBA: Concatenate cell data one below another using the formula not working

    D2 has reached its maximum length. Please explain in detail what you are trying to achieve.

  15. #15
    Registered User
    Join Date
    02-03-2021
    Location
    London
    MS-Off Ver
    2007
    Posts
    37

    Re: VBA: Concatenate cell data one below another using the formula not working

    Sir I am trying to merge the data in two text files to get the data in a single file (one below another).
    I thought the above way would be easy for me.

  16. #16
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,989

    Re: VBA: Concatenate cell data one below another using the formula not working

    I'm afraid that you will be limited by the maximum number of characters in a cell allowed by Excel.

  17. #17
    Registered User
    Join Date
    02-03-2021
    Location
    London
    MS-Off Ver
    2007
    Posts
    37

    Re: VBA: Concatenate cell data one below another using the formula not working

    Sir by giving the two file location one in C and another in D to merge and giving a folder/name.text in E, can we get the merged file using VBA? Not sure.

  18. #18
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,989

    Re: VBA: Concatenate cell data one below another using the formula not working

    It would be easier to help and test possible solutions if you could attach a copy of your file. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). See the yellow banner at the top of this page for instructions to attach a file. It would also help if you could post copies of the two files to be merged.

  19. #19
    Registered User
    Join Date
    02-03-2021
    Location
    London
    MS-Off Ver
    2007
    Posts
    37

    Re: VBA: Concatenate cell data one below another using the formula not working

    Sir, here is the example file. Is it clear?
    Attached Files Attached Files

  20. #20
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,989

    Re: VBA: Concatenate cell data one below another using the formula not working

    Thank you for the file. However, the sheet contains only the file paths of the two files to be merged and the output file. I need you to post the actual files themselves so that I can see the contents of the files and then you need to explain in detail referring to specific cells, which contents from each file you want to merge and where in the output file you want to place the merged contents.

  21. #21
    Registered User
    Join Date
    02-03-2021
    Location
    London
    MS-Off Ver
    2007
    Posts
    37

    Re: VBA: Concatenate cell data one below another using the formula not working

    I do not want to disclose the files in public so I wrote a private message, sir. I want to add the complete file1 data below complete file2 data and neet to get the out put in a new file.

+ 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] Concatenate values from Text Boxes into 1 cell not working
    By moosmahna in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-16-2017, 05:20 AM
  2. Replies: 5
    Last Post: 05-21-2015, 02:33 PM
  3. [SOLVED] Countifs and concatenate formula not working
    By ensmith in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-06-2014, 03:50 PM
  4. excel formula working on a cell which is having data in a data validation cell
    By sujithy007 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-16-2013, 04:18 AM
  5. excel formula working on a cell which is having data in a data validation cell
    By sujithy007 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-16-2013, 04:05 AM
  6. Concatenate Formula not working for all rows
    By jenkimbell in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-19-2012, 11:40 AM
  7. Can I concatenate text in cells to make a working formula?
    By Matt S. R. in forum Excel General
    Replies: 11
    Last Post: 11-11-2005, 11:50 AM

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