• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with combining data from multiple cells into one cell

The Answer

New Member
I am having a hard time with this. I want all the cells in column A1-A10 to have this format "ABC12345","ABC12346","ABC12347" and so forth in one new cell so that I can copy and paste them in one line. So far I managed to use a custom type \"@\" which puts a quote in them. I will have a lot of data into excel and doing this manually is a pain.

This is what I am trying to accomplish:
SQL query, select, scheme, data, criteria ("ABC12345","ABC12346","ABC12347")

All help is greatly appreciated.
 

Attachments

  • test.xlsx
    9.5 KB · Views: 4
If you are open to using Power Query (Get & Transform) this task is rather easy.
 

Attachments

  • Copy of test_Combine.xlsx
    25.9 KB · Views: 2
Try

1] Select A1:A10 >> extend your column A width wider enough for 10 words

upload_2019-3-23_9-58-11.png

2] In "Ribbon" >> Home >> Fill >> Justify

upload_2019-3-23_9-54-26.png

3] Hit "Justify" and become >>

upload_2019-3-23_9-57-21.png

Regards
Bosco
 

Attachments

  • upload_2019-3-23_9-50-0.png
    upload_2019-3-23_9-50-0.png
    13.3 KB · Views: 2
Yes @bosco_yip , but the required double quotes are missing then.
Other alternative, when available, is using TEXTJOIN(",",true,a1:a10)
Also double quotes would be missing.
 
upload_2019-3-23_19-4-33.png

Then,

In A3 enter a formula :

=FILTERXML("<x><a>"&SUBSTITUTE(A1," ","</a><a>")&"</a></x>","//a")

In the formula bar, hightlight A3 formula >> press F9, and A3 return an array containing text >>

={"ABC12345";"ABC12346";"ABC12347";"ABC12348";"ABC12349";"ABC12350";"ABC12351";"ABC12352";"ABC12353";"ABC12354"}

Regards
Bosco
 
Last edited:
Back
Top