July 19, 2014
Hi,
I need to find the relative position of a character within the cell
For example in the cell A1:
"Today is a Good day"
How many "a" letter are within the cell A1?
What are positions of the letter "a" (like in the 4-th, 11-en,19-th)
How can i extract the second "a", or the other positions?
Thank you,
Best Regards,
Dritan
VIP
Trusted Members
June 25, 2016
VIP
Trusted Members
June 25, 2016
Hi Dritan
This is a custom FUNCTION. Although it uses VBA, I don't consider this to be a macro. It behaves just like an ordinary FUNCTION.
Is there an reason for you not to use VBA? What are you using this for?
I have no idea how to get the position of all the "a" using array formula. Maybe some other members may have a solution.
Good luck.
Sunny
VIP
Trusted Members
December 7, 2016
Hello Dritan,
Check this page for more information in how to use Substitute function to count how often a character is used in a string.
Count characters in string
Your objective is to:
1) How many "a" letter are within "Today is a Good day" in cell A1?
2) What are the positions of the letter "a"?
3) How can i extract the second "a"?
4) How to extract the other positions?
By using functions you can use following methods.
1) =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1);"A";"")) <-- The result is 3. Check the link above for more information.
2) In cell D1 I write following function: =FIND("a";A1;1) <-- The result is 4.
3) In cell D2 I write following function: =FIND("a";A1;1+D1) <-- The result is 10.
4) In cell D3 I write following function: =FIND("a";A1;1+D2) <-- The result is 18.
As you can see this is not a dynamic approach, but it gives you at least a way forward as you don't want to use VBA.
Br,
Anders Sehlstedt
VIP
Trusted Members
June 25, 2016
VIP
Trusted Members
June 25, 2016
VIP
Trusted Members
December 7, 2016
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Considering Anders example: "Today is a good day" in cell A1, a dynamic formula can be built, using the AGGREGATE function:
=AGGREGATE(15,6,ROW(INDIRECT("A1:A"&LEN($A$1)))*SEARCH("a",MID($A$1,ROW(INDIRECT("A1:A"&LEN($A$1))),1)),ROW(A1))
If you copy it down, it will return a list of char "a" positions in the sample text string. If you want a horizontal list, simply change the last Row(A1) to Column(A1) and copy the formula to the right instead of copying it down.
The results cannot be displayed in a single cell, this is the case for any formula that returns an array of results.
Function 15 used in Aggregate function is the SMALL function. Many versions can be built based on this formula. For example, if you want to find the position of the last occurence of that char, use function 14 (LARGE), it's the only change you have to make.
Hope it helps.
1 Guest(s)