Forum Discussion
Solved
each letter of word in to separate cells
I need to extract the each letters of a word / words in to each separate cells.
(example attached)
or
I need to count the occurrence in of a particular letters in a table.
(example attached)
the purpose is to know the number of each letter so that I can order printing quantity accordingly.
Vimal_Gaur Provided you are using Excel365 or 2021, have a look at the attached file.
The green shaded areas is where I entered my formulas.
The discussion has moved on somewhat since I read it. What I did was to explore a more 'packaged' solution, unfortunately using 365 techniques to the full. I went as far as a single formula that uses Lambda function but is not itself a Lambda function.
= LET( characters, Explodeλ(name), letterCount, CountByLetterλ(characters), letterCount )The Lambda functions are
Explodeλ(list) "Separates strings into single characters" = LET( n, MAX(LEN(list)), MID(list, SEQUENCE(1, n), 1) ) and CountByLetterλ(chars) "Counts occurrences of each letter of the alphabet within an array of characters" = LET( alphabet, CHAR(SEQUENCE(26, 1, 65)), count, MAP(alphabet, LAMBDA(chr, SUM(SIGN(chars = chr)) )), HSTACK(alphabet, count) )Because I had converted the list of names to an Excel table, all I had to do was copy/paste the new list of names to the table to have it resize.
- Thank you Sir,
This is for 365, any solution for MS excel 2021?Yes, it is possible to deconstruct the Lambda functions, though it is not something I would usually do.
Helper ranges are used in place of LET local named variables and the code within each Lambda function is placed directly within the formula cell. References passed by name are replaced by hard-wired references. One advantage of using helper ranges is that some, otherwise very good, Excel built-in functions, like COUNTIFS, only accept range references and fail with arrays.
The result of this process will be very close to one of the solutions you already have. To roll the solution back to pre-dynamic-array days one would have to use relative referencing to pick out the elements of the solution array or resort to CSE. Some would see such a process as achieving 'simplicity'; others see it as creating an error-prone mess; it all depends on one's view point.
Vimal_Gaur Provided you are using Excel365 or 2021, have a look at the attached file.
The green shaded areas is where I entered my formulas.
Yes, I misunderstood, but eventually got it. Thank you so much again. That was brilliant and simple.
I have also written something in VBA that will also extract, attached is the file.- Thanks for reply.
I know the COUNTIF formula, I can do the counting myself.
I need to extract the letters in separate cells or if you can count the letters in the whole string.
e.g. 1. "Vimal Gaur" in a single cell B11 (find how many A are there in this word)
e.g. 2. "Rozer Ramon is lives right on bank of river" now find occurrence of each letter in this string.Vimal_Gaur I merely replicated what was in your file. But WITH formulas. Did you note the formula in C11 and below? That's splitting each text string in B into separate cells. If that's not what you wanted, I misunderstood.
