Create an Index to sort A-Z

[kabookie]

Today’s challenge was to create a scrollable index for a list of companies that groups them by the first letter of the company name. It took a bit of figuring out, but I am very happy with the result and want to share so it can help someone else. I had searched but was not able to find the answer in the forum.

So here is a quick loom:

And here the formula that made it work:
={“Index”; ARRAYFORMULA(IFNA(REGEXEXTRACT(A2:A, “.”)))}

And thank you @Robert_Petitto for this video which helped greatly: https://youtu.be/3C0B7nHLcCA

[ThinhDinh]

Lovely idea.

[Nadir_Abdelrahman_MD]

Thanks for sharing.

[Eric_Penn]

Excellent! Thank you for taking the time to make this tutorial.

[Darren_Murphy]

Nice job!

Just a comment on your choice of function for the arrayformula. Whilst REGEXEXTRACT() obviously does the job, it’s probably a bit of overkill for this use case. There are lots of ways you can extract the first character from a string, but probably the simplest would be by using LEFT()

Note that while LEFT() accepts a parameter indicating how many characters to extract, it is optional and defaults to 1. Which it makes it perfect for your use case

So your arrayformula could be:

={"Index"; ARRAYFORMULA(IFNA(LEFT(A2:A)))}

[kabookie]

@Darren_Murphy thank you! I did not run across that option in my research.

[Brad_Legassick]

@kabookie thx for the tip, Glide encourages out the box problem solving!

[wjcv06]

LEFT, RIGHT, SUBSTRING should be part of the math column

[Jacktools_Net]

Array.size
Array[index] to get a specific value fron an array column.