Hi - Dave here.
Happy Friday!
I've been bothered for a long time that there isn't an easy way to convert a text string in Excel to an array of characters. Up to now, the best way is to use SEQUENCE with the MID and LEN functions like so:
=MID(A1,SEQUENCE(1,LEN(A1)),1)
This formula counts characters with LEN and then uses the count to generate a numeric sequence with SEQUENCE. Then, it uses the numeric sequence to ask MID for one character at a time...get 1 character starting at position 1, get 1 character starting at position 2, etc. Ugh. It works, but it's not pretty.
I was thinking about this recently, and I suddenly realized we can now solve this problem with the REGEXEXTRACT function:
=REGEXEXTRACT(A1,".",1)
Nice. One function, simple and elegant. My kind of formula 🙂
[
Download the workbook and read the full explanation]
This is another great example of how new functions in Excel are changing the game – making hard problems much easier to solve. Read the article above for the full explanation, then download the workbook to try it out yourself.
Note: REGEXEXTRACT is only available in Excel 365. In the article, I also explain two ways to solve this problem in older versions of Excel.
Exceljet Chatbot
Have a question about Excel? Try our new chatbot, which is trained on Exceljet.net content:
https://chat.exceljet.net/
Note: I am always revising content on the website so sometimes the chatbot lags behind the latest information.
Excel formulas
We maintain a list of over 1000 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
The Exceljet newsletter is free and sent weekly on Fridays. Each week, I take a detailed look at how to solve a specific problem with an Excel formula. You can sign up on our home page.