Multiple matches in a cell separated by line break

Dear excel community,

I am facing the following problem to solve. In the range F2:G4 I have a list of countries with corresponding IDs. In column A I have quite random strings that might contain name of a country or countries. My task is to find a match in that string using F2:F4 as a source and bring the IDs of matching countries separated by a line break in a cell in column B.

Country names are only given as 1 word starting with capital letter or all-capital abbreviations, such as RSA (Republic of South Africa). As well, different separators might be used for input string. Most commonly ',' or ' ' or ';'.

Would appreciate a lot some suggestions

Capture.JPG

Best regards,
P

In your real data are the "countries" always just one word? The only logic in determining the order in which a "country" appears seems to be the capital letter. If you have multi-capitalised names, like El Salvador... that approach won't work.

Hi Glenn,

Thanks. I would say that the order is not critical. The most important thing is getting and showing the matching countries. I have changed the attachments now to reflect that.

Determining the order of the countries IS critical. Once you know that Russia is in the nth position out of Y positions, in a cell; you can extract the appropriate number.

Of course, if there is ALWAYS at least one instance of the coutnry appearing in a cell ON ITS OWN, then there is no problem at all. However, since yoou have asked this question, I assume that this cannot be guaranteed.

So, please answer my Q in my previous post.

... but no such examples in your sample file.

Your sample data needs to reflect the true data in every facet.

Originally Posted by Paulius03

There are multi-capitalized names, such as South Africa.

And then ...

Originally Posted by Paulius03

To simplify the problem I will edit my original post saying that country names are only 1 word or all-capital abbreviations, such as RSA (Republic of South Africa).

Too many contradictions for me. It's really important to be ABSOLUTELY CLEAR about the nature of your data - saying one thing and then the opposite almost suggests that you are not clear yourself, so how can we really hope to address the problem properly for you?

Based on the inputs of other members of the forum I made some adjustments to my initial problem conditions. Sorry for the confusion, but what should I do if there is a change? Should I create a new thread?

Originally Posted by Paulius03

Based on the inputs of other members of the forum I made some adjustments to my initial problem conditions. Sorry for the confusion, but what should I do if there is a change? Should I create a new thread?

NO! Do what was suggested earlier and add the changes to a new post to the thread - that way, things remain chronological. Also, you can attach an updated file to the correct post chronologically.

Depending on your answer to the next question... this may be impossible (or at least beyond my ability).

Your file suggests that you can have countries strung together with NO separator like this: ChinaRussiaBrazil

is that REALLY the case?

Please try

Formula:

=--MID(SUBSTITUTE(VLOOKUP("*"&F2&"*",$A$2:$B$6,2,),CHAR(10),REPT(" ",99)),MATCH(1,FREQUENCY(FIND(F2,VLOOKUP("*"&F2&"*",$A$2:$A$6,1,)),SEQUENCE(99)/(ABS(CODE(MID(VLOOKUP("*"&F2&"*",$A$2:$A$6,1,),SEQUENCE(99),1))-77.5)<13)),)*99-98,99)

Like i said... beyond my ability!!

You should NOT edit attachments AFTER replies have been received... it makes the replies unintelligible. Add any updated attachments in a new post in the same thread.

Try at B2

=TEXTJOIN(CHAR(10),,SORTBY(REPT($G$2:$G$5,ISNUMBER(SEARCH($F$2:$F$5,A2))),SEARCH($F$2:$F$5,A2)))