ARRAYTOTEXT() Function Excel - 5 Practical Examples
HTML-код
- Опубликовано: 2 июл 2024
- Excel File: www.teachexcel.com/excel-tuto...
Microsoft Office Doc: support.microsoft.com/en-us/o...
5 Practical uses of the ARRAYTOTEXT() Function in Excel.
It works well for creating nice visual lists output into a single cell and also array-formatted text output, complete with the semicolons and the curly braces around it.
This function kind of seems to sit between the CONCAT() and the TEXTJOIN() functions and it is only available for Excel 365.
The examples in this tutorial include:
- Making simple numbered lists in Excel.
- Making text lists in Excel.
- Making dynamic lists by nesting functions.
- Outputting unique lists of values from a Table.
- Creating useful lists of values, such as for sending emails.
SEQUENCE() Function Excel: www.teachexcel.com/excel-tuto...
TEXTJOIN() Function Excel: www.teachexcel.com/excel-tuto...
Excel Courses:
- VBA/Macro Course: www.teachexcel.com/vba-course...
- Building Professional Forms: www.teachexcel.com/premium-co...
- Email Course: www.teachexcel.com/premium-co...
TeachExcel.com
#msexcel #howto #tutorial
This is a beta function for Excel 365 I found yesterday and thought it was interesting. Let me know if you have any other uses for it :)
What I took from this is the unique function. Genious. Cheers.
I'm glad I could help :) It makes me think that I should flesh-out that example a bit more as it really does make life so much nicer now.
@@TeachExcel And combining UNIQUE and SORT in one statement is pretty helpful, too.
MS is moving more and more database functionality (the "what I want" vs "how do I get what I want", or declarative vs procedural) into Excel. Abstraction is a good thing. Now, if Microsoft could incorporate Power Query into Access and make its interface less klunky (stop reformatting my SQL into an unreadable mess!), that would be killer. One can dream.
Cool Tutorial,I Have Never Used ARRAYTOTEXT() So Just Learned A New Fuction...Thank You Sir :)
I'm glad I could show you something new Darryl :)
This video is amazing , thank u
Thanks, great tutorial. Subscribed and will have a look through some of your other vids :)
Interesting! Thanks for sharing this :)) Thumbs up!!
This is great stuff!
Thanks! I'm glad you think so Aaron :)
@@TeachExcel any guesstimate on when the macro/vba course will be complete? Waiting patiently, excited for that to come out.
I really appreciate that Aaron! I have learned to not make another prediction, but I have finished all of the big stuff, and now am in a video editing phase and adding intros to every section. There is a chaaaaance that I made the course a little comprehensive, but I must say that I am very happy with it and I think once someone takes it, they will feel comfortable tackling any task in vba. Once again, I am very sorry that I have not been able to meet my deadlines for publishing it!!!
"Excel"lent..!!! just a quick note, when you want the column in a table i.e. Table[Mrf]. hover just above the column header and you will see a thick black down arrow. Click and you will insert the table column into your formula. Makes it much easer when you have 1000's of rows of data.
you could use it in VBA maybe...
maybe to convert data to collections (unique keys) or arrays.
with
worksheetfunction.unique()
or
worksteetfunction.arrayToText()
especially when it is data you just need in one cell, separated with comma or semicolon. 👍
Thanks.
Definitely liking your thoughts on using it in VBA! I also think Excel was or is planning to use it for something else that is not yet fully implemented.
Excellent tutorial. How long has it been in operation? Thank you!
Thank you! You're very welcome Ivan :) You know, this is funny because I did some digging and it seems to have been released last year, or maybe earlier, for some people, and it seems like they had bigger plans for it relating to newer data types, but maybe they just put that stuff on the back-burner but left this guy in there. I found the function by accident this week.
@@TeachExcel I did not know about this function.
A lot of LET, a lot of LAMBDA and then you come across this feature!
My face was like I had landed on planet Earth.
Awesome
Thanks Vikus! :)
Thank you very much, your videos are very informative for me and you have a calm voice. But you keep moving your mouse in circles and it makes me dizzy, :)
Have a nice day
Oh I'm sorry about that! I actually thought that would make the videos better for people - I will try to be less spastic with my movements.
Great! Только как часто и где на практике необходимо заменять массивы на текст в строчку..?
English is easier for me, but I think i got it lol - I'm not sure how useful this function will be going forward compared to textjoin, it depends what they do with it. But combining a range into a single cell for output is quite useful as a future tutorial will show you regarding outputting slicer selections in a single cell.
How did you find that beta function?
I was just messing around with arrays and looking for functions that might be new to slice and dice them like you can do in vba and found this guy and investigated what it was. I not sure they are even planning to keep this in there as almost no mention of it is made online beyond a very basic usage.
Couldn't you also write =ARRAYTOTEXT(H23#)?
cost for workshop
Hi stamble to your video tutorial
Im looking for a formula and how to set up to the output. I want
Letter separated per cell
Pattern 1: LWLWLWLWL
Pattern 2: LLWLLWLLW
Patter 3: WWWLWWWLW
Patter 4: LLLLLLLLL
Pattern 5: WWWWWWWWW
Results
LLWLWWWWWLWWLLLLWWLWLWL---
What I want is to predict the possible letter that will come out next base on the establish pattern.
i hope you can give me a guide
1= the whole - system! 2= tiny ()-call - you have - zero array.