Register
Log In
Unanswered
Home
July 19, 2014
Offline
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
Dashboards
June 25, 2016
Offline
Hi Dritan
Try this attachment.
How can i extract the second "a", or the other positions?
1st, 2nd 3rd "a" is still an "a" when extracted, so why extract it?
Sunny
July 19, 2014
Offline
Thank you so much SunnyKow for the answer.
You are right about the extracted when the character is still "a".
But is there any way to do with array formula (I want to avoid macro)?
Best regards,
Dritan
Dashboards
June 25, 2016
Offline
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
Members
Dashboards
Power Query
Power Pivot
Power BI
December 7, 2016
Offline
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.
https://exceljet.net/formula/count-specific-characters-in-a-cell
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
Dashboards
June 25, 2016
Offline
Hello Dritan
If you want to use the formula suggested by Anders, I suggest you use SEARCH instead of FIND as SEARCH is not case-sensitive.
Sunny
July 19, 2014
Offline
Great solution,
Thank you Anders Sehlstedt for your help.
Once again I want to thank you SunnyKow for your suggestion:)
Best Regards,
Dritani
Dashboards
June 25, 2016
Offline
Thanks for your feedback. Glad that we could help.
I am also happy to see that more members are willing to contribute their ideas and solutions to help and this is a very good sign.
Sunny
Members
Dashboards
Power Query
Power Pivot
Power BI
December 7, 2016
Offline
Hi Dritan,
Great that the formulas works and thank you for the feedback.
Br,
Anders
November 8, 2013
Offline
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.
July 19, 2014
Offline
Hi Catalin Bombea
Oh that's great solution :),
But Aggregate function is for the Excel 2010 and above, what about the Excel 2007, or can i replace the Aggregate Function with another Function?
Best Regards,
Dritan
Most Users Ever Online: 57
Currently Online:
27 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Akshatraj Adig
Liesie Marais
Moet Lewis
Michelle Anderson
Veronica Gallego
krishnaraj r
Dharan Prakash Mishra
Md. Rahman
Lilian Vo
Jane Kumwenda
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
