Combine or Transpose Text from Multiple Rows
HTML-код
- Опубликовано: 5 ноя 2014
- Want to transform your list of values into something you can use differently? Like converting multiple rows of values into a single row and concatenate (combine) the values into one comma delimited cell? Learn how to combine cell from multiple rows or columns into one cell without a space, with space or as comma separated values (csv); all without creating any macros.
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltraining101.com/p/tr...
📚 Excel Books & Tech Gear ➜ www.amazon.com/shop/dough
⚙️ Tools: Screencasting ➜ techsmith.z6rjha.net/5Qe53
⚙️ Tools: Microsoft Office ➜ microsoft.msafflnk.net/rKL0G
⚙️ Tools: TubeBuddy ➜ www.tubebuddy.com/et101
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to www.buymeacoffee.com/dough
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~
Thank you Doug!! You are the best.
You're welcome, thanks for the comment!
Well explained. Great step by step explanation. Thank you for your video.
Hi Marshall Jackson, thanks for the comment!
This is super helpful. Thanks a lot!!!
Glad it helped!
This is a perfect video, I was trying to create a script that would ping multiple IP addresses in a spreadsheet, this helped me a lot, thanks .
You're welcome, glad it helped!
Thanks, this helped me a lot!
Glad it helped!
Obrigado Doug.
De nada!
This SO saved the day in my world. Thank you for sharing.
Glad it helped!
Just what i needed today. thank you :)
You're so welcome!
Thank you. Very helpful
Glad it helped!
You are an absolute hero!
Hi DiscJokeyDarkSigns, thanks for the kind words!
Very good and helpful. Tks!
Glad it was helpful!
thank you, very helpful
Glad it was helpful!
Thank you So much Sir, you are great.
So nice of you!
Thank you so much for this ♥
You’re welcome!
Thanks 😊
Welcome 😊
Hello Dough, Its help me a lot. Thank you so much🙂🙂🙂🙂
Glad to hear that!
Thank you sir
You're Welcome!
Thank a lot....👍
Welcome 😊
VERY NICE,
Thank you! Cheers!
Lifesaver!!
Glad it helped!
oh my god! you life saver - bless you!!!
Glad it helped!
Hi
Thanks for short and helpful video
I used your first formula that you showed in video for more than 10 rows of text to 1 cell.writing formula and highlighting it from back till transponse and f9 finally removing curly brackets and enter.now i need to your help that can this formula be applied again and again for different text of rows to one cell(in order to move long lines of text in multiple rows to single cell.) =concatenate(transponse(A1;AA14)
Hi Zie, thanks for the comment!
For the 3rd example if you reverse the formula like so =CONCATENATE(B1,",",A2); the list will show in order Item 1 to Item 18
For my case...After removing the curly braces, when I press Enter it doesn't work
Hello Dough, Very Nicely explained. Thank you. I would like to add single quote and comma to each no. like 'Item1', 'item2' . Is it possible using this formula? I tried adding "&" function but results are not as i want.could you please help!
Thanks, maybe include the CONCAT function Join Cell Values with CONCATENATE, CONCAT or TEXTJOIN Functions
ruclips.net/video/4GAjQtMgGsQ/видео.html
Font colour is getting changed from the original data. How to rectify that?
YOU SAVE MY LIFE
Thanks M A, glad it helped!
already used line break function but that line brake in a cell. I want to be each of the cell in a column. How can I do. My required is different things with this video tutorial. This video "turn this" "into this", but I want to do "into this" to "turn this". Thanks
how would I accomplish the reserve, my data is separated by commas all in one cell, and I need it separated into one column?
one of these should help ruclips.net/video/QKRSF7TM88M/видео.html
Please add transpose to your title. It will make it easier for people that need to transpose their work and for those that don't. Thanks!
+Doug H You bet. :)
Hi Kelly Peissner, thanks for the comment!
Hi there, I had a column of 296 item numbers I was using the following function =(Concatenate(Transpose(A1:A296&","&" ") this is because I wanted a space inserted between the upc's and commas as well, but when I do the F9 function and remove the curly que's I get the following error " You've entered too many arguments for this function. Is there a limit to how many items you can combine with this function?
depending where the selection was made and then F9. That would have effectively executed the function and if it's nested in another function and the arguments weren't valid, then you'd get the error.
@@DougHExcel Thanks for responding, I found the answer, there is a 1028 text limit to using the concatenate function, instead I could have just placed a comma in a cell next to my six digit number and use =concat(A2:B296) and it combined them with a comma and transposed without having to tell the formula to transpose.
hello acctually i want to do reverce of it i have data in one box seprated by space i want it in cells
ruclips.net/video/cOfaYdL55AE/видео.html
HI
CAN YOU FIX IT
IM TRYING TO COPY MANY ROWS WITH TEXT TO 1 CELL BUT THIS ERROR IS COMING. ITS NOT PICKING 1 ROW. I DONT KNOW WHY.
HERE IS THE FORMULA. I hightlighted it till transpose and then pressed f9 key and removed CURLY brackets with closing with parenthesis and enter.
=CONCATENATE(TRANSPOSE(I73:I78)
Hi Zie, sorry I don't do consulting :-( ....but try a post on the mrexcel.com forum!
Great tutorial. But is there a function where you can do the reverse?
Ie. I have text that is written with commas (Item 1, Item 2, Item 3 etc.) and I want each text between commas to be transposed into separate rows
Is that possible?
Try this one ruclips.net/video/cOfaYdL55AE/видео.html
How to make this works by applying to different row in one cell. Example :-
A
B
C
D
E
F
*not as A, B, C, D, E, F
not sure what you mean
@@DougHExcel well if we merge several rows, the result will only delete everything else except for the top cell content. But right now I want to maintain the position of the row, but make it into only one cell
maybe this will give insight
ruclips.net/video/cOfaYdL55AE/видео.html
Waste of time to see this video
Thank you Doug!! You are the best.
Hi CA. Ravi Goyal, thanks for the kind words!
@@DougHExcel I learned best from your videos Doug!.