Find the (n) matched of string in a string.

[gaixixon]

Is there any SIMPLE excel function that can find the (n)th matched of a string in a string?
I need to extract the number and text, separated by "+" as in example below:
In cell A2 I have value: "abcd+123+xyz".
This is what I'm doing:
In cell A3, I add two more "++" to the end of A1 cell value

Please Login or Register  to view this content.

Cell A4 I have formular:

Please Login or Register  to view this content.

and cell A5:

Please Login or Register  to view this content.

and cell A6:

Please Login or Register  to view this content.

Is there anything like this:

Please Login or Register  to view this content.

without writing VBA code?

I don't think there is such function, and I can find one more solution to my problem via this thread:
http://www.excelforum.com/excel-form...r-x-and-y.html

[teylyn]

Look here

http://www.excelforum.com/excel-gene...r-in-a-st.html

[humdingaling]

simple....not quite
generally a combination of formula's like you have done would be required

based on your example....you want to find the string after the 2nd occurrence?

here is how i would of done it

assuming a2 is the cell you want to extract

Please Login or Register  to view this content.

char(95) [which is _ ] can be replace with anything you want...but should be something that does not appear in string itself
What this formula does is find the 2nd occurrence of "+" and replaces it with "_"
then it finds where _ is and left formula kicks in to return text up to where "_" was found

[Green Crocodile]

Here is one you can use. It's not the most elegant, but works. Just put in whatever cell and drag down. This too assumes the text you want to extract is in A2.

Please Login or Register  to view this content.