1

I have a list of cells in Excel, all in column I. Each of the cells has a list of countries and/or regions.

I would like to build a formula that will search the text in the cells in column I for specific strings and return those same strings in the same row of column J. If more than one string is found, then I would like all strings found to be in column J.

The independent strings I would like to look for in column I are: "Africa", "North Africa", "East Africa", "South Africa", "West Africa" and "Central Africa".

If a cell on column I has "Algeria, France, Portugal, East Africa, North Africa, Zimbabwe", then the desired result on column J would be "East Africa, North Africa".

I have managed to build a formula on column J that will look for on of the strings:

=IF(ISNUMBER(SEARCH("Africa", I2)), "Africa", "")

I have also managed to make nested IF statements that will look for each string independently and return, on column J, whichever is first found. However, in this case, only the first string found is returned.

How can I build a formula that will both search for all the strings in one cell (without stopping when it finds the first one) and return them all in column J?

Thank you

Improve this question

2 Answers 2

Reset to default
2

Try this small User Defined Function:

Public Function GetKeyWords(rng As Range) As String
    bry = Array("Africa", "North Africa", "South Africa", "East Africa", "West Africa", "Central Africa")
    ary = Split(rng.Text, ", ")
    For Each a In ary
        For Each b In bry
            If a = b Then GetKeyWords = GetKeyWords & ", " & b
        Next b
    Next a
    GetKeyWords = Mid(GetKeyWords, 3)
End Function

enter image description here

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=myfunction(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

If you have Office 365 Excel then you can use the following array formula:

=textjoin(",",TRUE,IF(ISNUMBER(SEARCH(", " & A1:A6 & ", ",", " & I1 & ", ")),A1:A6,""))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

Change A1:A6 to the range that contains the region names desired.

Improve this answer

1 Comment

Is there a similar formula that will work in reverse order. That looks for and exclude keywords from list. If a cell on column I has "Algeria, France, Portugal, East Africa, North Africa, Zimbabwe", then the desired result on column J would be "Algeria, France, Portugal, Zimbabwe".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.