Как я могу вернуть список определенных слов, найденных в содержимом ячейки?

Question

У меня есть два столбца, один со строкой текста (A1), другой пустой (A2).

A1 выглядит примерно так:

«Краткие сведения о Южной Африке, Родезии, Восточном Суэцком (включая советское военно-морское расширение), арабо-израильском споре, угоне, отношениях Восток-Запад, НАТО, Великобритании и европейских сообществах»

Мне нужно ввести названия стран, представленных в A1 в A2 . Например, выше я бы въехал в Rhodesia, Soviet Union, Israel, United Kingdom .

Есть ли формула, которую я могу использовать, чтобы найти конкретные слова и затем поместить их в A2?

Answer 1

Вам нужно будет составить цепочку функций. Следующие шаги работали в Excel 2010 и предполагают, что у вас есть только вышеупомянутый текст в A1:

1. Скопируйте следующую формулу в ячейку B1:
   • =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,","," "),"-"," "),"(",""),")","")
   • Эта формула учитывает только знаки препинания, включенные в исходный текст. Чтобы поймать больше знаков препинания, вам придется заключить эту формулу в другую ЗАМЕНУ (<< текущая формула >>, << пунктуация в кавычках >>, << либо "", либо "" в зависимости от желаемого результата >>) ,
2. Имя ячейки B1 "SearchText" (без кавычек). Вы можете сделать это следующим образом:
   • Нажмите Alt,M,M,D чтобы открыть диалоговое окно New Name . Перезапишите предложенное имя и нажмите Enter .
   • Нажмите на поле « Name Box слева от панели редактирования формулы, затем введите имя и нажмите Enter .
3. Введите свой список стран в столбец D , начиная с ячейки D1 .
4. Выберите столбец D и назовите его "Страны", как указано ранее.
5. В ячейку C1 введите следующую формулу:
   • =IF(IFERROR(SEARCH(CONCATENATE(Countries," "),SearchText),FALSE),Countries,"")
6. В ячейку C2 введите следующую формулу:
   • =IF(IFERROR(SEARCH(CONCATENATE(Countries," "),SearchText),FALSE),IF(C1="",Countries,CONCATENATE(C1,", ",Countries)), C1)
7. Скопируйте C2 и вставьте его до конца столбца, заканчивая (предположительно 196 стран) ячейкой C196 .
8. В ячейку A2 введите следующую формулу: =C196
   • Вы можете скрыть или сгруппировать столбцы от B до D чтобы привести в порядок вещи.

Каждый раз, когда вы вводите новый бит текста в A1 , формулы пересчитываются, а ваши результаты отображаются в A2 . Надеюсь, это поможет.

Answer 2

Попробуйте эту небольшую пользовательскую функцию:

Public Function XtractNations(t As String) As String
    Dim Nations(1 To 48) As String
    Dim s As String
    Nations(1) = "China"
    Nations(2) = "Japan"
    Nations(3) = "Germany"
    Nations(4) = "France"
    Nations(5) = "United Kingdom"
    Nations(6) = "Brazil"
    Nations(7) = "Russia"
    Nations(8) = "Italy"
    Nations(9) = "India"
    Nations(10) = "Canada"
    Nations(11) = "Australia"
    Nations(12) = "Spain"
    Nations(13) = "Mexico"
    Nations(14) = "South Korea"
    Nations(15) = "Indonesia"
    Nations(16) = "Turkey"
    Nations(17) = "Netherlands"
    Nations(18) = "Saudi Arabia"
    Nations(19) = "Switzerland"
    Nations(20) = "Iran"
    Nations(21) = "Sweden"
    Nations(22) = "Norway"
    Nations(23) = "Poland"
    Nations(24) = "Belgium"
    Nations(25) = "Argentina"
    Nations(26) = "Austria"
    Nations(27) = "Thailand"
    Nations(28) = "South Africa"
    Nations(29) = "United Arab Emirates"
    Nations(30) = "Venezuela"
    Nations(31) = "Colombia"
    Nations(32) = "Denmark"
    Nations(33) = "Malaysia"
    Nations(34) = "Singapore"
    Nations(35) = "Chile"
    Nations(36) = "Hong Kong"
    Nations(37) = "Nigeria"
    Nations(38) = "Egypt"
    Nations(39) = "Philippines"
    Nations(40) = "Greece"
    Nations(41) = "Finland"
    Nations(42) = "Israel"
    Nations(43) = "Pakistan"
    Nations(44) = "Portugal"
    Nations(45) = "Ireland"
    Nations(46) = "Algeria"
    Nations(47) = "Peru"
    Nations(48) = "Kazakhstan"
    s = ""
    For i = 1 To 48
        If InStr(1, t, Nations(i)) > 0 Then
            s = s & ", " & Nations(i)
        End If
    Next i
    XtractNations = Right(s, Len(s) - 1)
End Function

Вы можете добавить в список столько стран, сколько захотите.