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

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

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

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

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

Great solution,

Thank you Anders Sehlstedt for your help.

Once again I want to thank you SunnyKow for your suggestion:)

Best Regards,

Dritani

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

Hi Dritan,

Great that the formulas works and thank you for the feedback.

Br,

Anders

Considering Anders example: "Today is a good day" in cell A1, a dynamic formula can be built, using the AGGREGATE function:

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.

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

Hi Dritan

Maybe you can give this a try.

Hi SunnyKow,

As always you bring very clever solution.

Thank you very much

Dritan