Excel Marco How to put multiple rows text values into single cell using macro

Hi,

I am again with a big problems and head scratching also. But you guys are gurus of this excel..

macro.jpg

in picture you can see colum E have 10 cells and I want to make a macro to put all these values into cell f1

Thanks

I also attached excel file so you can see the data I wants as it has some non english words, which is requirement.

excelforumssheet.xlsx

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Originally Posted by spa3212

Hi,


in picture you can see column E have 10 cells and I want to make a macro to put all these values into cell f1

Thanks

I also attached excel file so you can see the data I wants as it has some non english words, which is requirement.

…..

Hi, spa3212,
. That new Title is better !…

.. So here are a few codes to get you going. I checked them all out on your file and they seem to work..

…Code 1: Simplest “Spreadsheet interaction” type code.

…Code 2: Typical “VBA Array” type alternative.

…Code 3: Similar to code 2, but using Join Function

…Code 4: Evaluate Range “One Liner” type code. – Note: unfortunately this does not work for more than about 5 rows of your data, due I expect to the evaluate 255 string character limit. But I include it for completeness – Who knows maybe sometime in the future someone looking into this thread may have an updated Excel version where this limit is increased!!

..
Have Fun, get back if you need more help…

Alan Elston

…………..

Here are the 4 codes:

Please Login or Register  to view this content.

The first three work on your data

Using Excel 2007

-	E
1	???? → super<br><P>
2	????? ?????? → super<br><P>
3	?????? ??????? → super<br><P>
4	???????? → Delicate,demosthenian,Demosthenic,Exquisite,hunky-dory,super<br><P>
5	???? → grand,magnificent,imposing,grandiose,super,impressive<br><P>
6	??? ?? → from the top,from above,over,out,in addition,super<br><P>
7	????? → perfect,good,glorious,splendid,dandy,super<br><P>
8	???? ???? → enormous,immeasurable,hulking,gigantic,giant,super<br><P>
9	???? → further,great,super,extra,undue,king-size<br><P>
10	→ <br><P>

Sheet1

To give you this:

Using Excel 2007

-

F

1

???? → super<br><P> ????? ?????? → super<br><P> ?????? ??????? → super<br><P> ???????? → Delicate,demosthenian,Demosthenic,Exquisite,hunky-dory,super<br><P> ???? → grand,magnificent,imposing,grandiose,super,impressive<br><P> ??? ?? → from the top,from above,over,out,in addition,super<br><P> ????? → perfect,good,glorious,splendid,dandy,super<br><P> ???? ???? → enormous,immeasurable,hulking,gigantic,giant,super<br><P> ???? → further,great,super,extra,undue,king-size<br><P> → <br><P>

Sheet1

… Note the actual File results looks correct.. the screen shot I am using here does not appear to support your characters.

Originally Posted by spa3212

........................
many many thanks...I just tried 1st code and it works like charms...even 20 raw data..m using 2013

. Hi,
. Thanks for the feedback,

(. I think the character limit in a cell is somewhere around 32767, so I think most of my codes will work for a large number of columns. Even with your long strings you will take some time to reach that limit.!!!
..( My last code falls down I think, due to the string argument limit 255 characters of the Evaluate function, as I mentioned, but there are other strange limits in the code which for interest I am investigating… )… )

Alan

P.s. It is somewhat unusual to have so much text in a cell, but your choice!!