Encontre e obtenha palavras específicas por pontuação, mas remova a pontuação do resultado

Question

Eu tentei que tudo fosse criado com uma solução para esse problema, mas fiquei perplexo e pude usar alguma ajuda. Qualquer feedback que vocês possam me dar seria ótimo.

Eu tenho um conjunto de dados que segue basicamente a estrutura de sentença abaixo em cada célula:

Twelve little pigs rolled down the hill at Huckleberry, Farms: Citizen from Huckleberry

Estou tentando criar uma fórmula do Excel que capture a palavra "Huckleberry" sozinha em uma célula separada da qual os dados são preenchidos (Cell B2 In Example).

A estrutura de "Huckleberry" sempre tem "," anexada a ela e sempre precede a seguinte palavra que tem dois pontos (:) anexados a ela, portanto segue basicamente o formato de:

Huckleberry, Fazendas:
Bluefield, Meadows:
Buttercream, Vale:

Cheguei perto do que preciso usando a fórmula:

=TRIM(MID(SUBSTITUTE(B2," ",REPT(" ",99)),MAX(1,FIND(",",SUBSTITUTE(B2," ",REPT(" ",99)))-50),99))  

Mas ele retém a vírgula que me dá "Huckleberry", quando o que eu quero é "Huckleberry".

Eu preferiria uma opção, se possível, que usa o ":" de "Farms:" como ponto de partida, trabalhando da direita para a esquerda para encontrar o "," em "Huckleberry", como às vezes existem vírgulas que vêm antes da vírgula inicial encontrada em "Huckleberry". Mas neste ponto, qualquer solução de fórmula para essa questão seria muito apreciada.

    

Answer 1

Aqui está uma fórmula normal simples que funcionará em qualquer versão do Excel:

=MID(B2,FIND("§",SUBSTITUTE(LEFT(B2,FIND(":",B2))," ","§",LEN(LEFT(B2,FIND(":",B2)))-LEN(SUBSTITUTE(LEFT(B2,FIND(":",B2))," ",""))-1))+1,FIND(",",B2,FIND("§",SUBSTITUTE(LEFT(B2,FIND(":",B2))," ","§",LEN(LEFT(B2,FIND(":",B2)))-LEN(SUBSTITUTE(LEFT(B2,FIND(":",B2))," ",""))-1)))-FIND("§",SUBSTITUTE(LEFT(B2,FIND(":",B2))," ","§",LEN(LEFT(B2,FIND(":",B2)))-LEN(SUBSTITUTE(LEFT(B2,FIND(":",B2))," ",""))-1))-1)

Ele retornará corretamente a palavra antes do primeiro dois pontos se essa palavra for seguida por uma vírgula, independentemente de quantos outros dois pontos com um par de palavras antes deles estão na cadeia de dados. / p>

A falha com esta fórmula é que se a palavra antes da palavra antes do primeiro cólon não for seguida por uma vírgula, ela retornará um erro.

Explicação:

Esta fórmula é mais fácil de entender se você usa quatro células auxiliares:

Asfórmulasnascélulassão:

B3:

=LEFT(B2,FIND(":",B2))

B4 :

=LEN(B3)-LEN(SUBSTITUTE(B3," ",""))

B5 :

=SUBSTITUTE(B3," ","§",B4-1)

B6 :

=FIND("§",B5)

B7 :

=MID(B2,B6+1,FIND(",",B2,B6)-B6-1)

A solução completa é simplesmente o resultado de inserir as fórmulas para B3 , B4 , B5 e B6 em B7 .

Notas:

Na verdade, manter as quatro células auxiliares, em vez de transformar as fórmulas em uma, tornaria a fórmula muito mais fácil de manter.

Melhor ainda seria manter a fórmula das quatro células auxiliares em uma planilha de "desenvolvedor", facilitar as modificações e usar a fórmula agregada na planilha de produção.

    

Answer 2

Esta fórmula de matriz retornará a palavra com uma vírgula antes da palavra com dois pontos, independentemente de quantas vírgulas aparecerem antes ou depois:

=CONCAT(IF((RIGHT(TRIM(MID(SUBSTITUTE($A$1," ",REPT(" ",99)),(ROW($1:$40)-1)*99+1,99)))=",")*(RIGHT(TRIM(MID(SUBSTITUTE($A$1," ",REPT(" ",99)),ROW($1:$40)*99,99)))=":"),LEFT(TRIM(MID(SUBSTITUTE($A$1," ",REPT(" ",99)),(ROW($1:$40)-1)*99+1,99)),LEN(TRIM(MID(SUBSTITUTE($A$1," ",REPT(" ",99)),(ROW($1:$40)-1)*99+1,99)))-1),""))

Sendo uma fórmula de matriz, ela precisa ser confirmada com Ctrl-Shift-Enter em vez de Enter ao sair do modo de edição.

CONCAT foi introduzido no Office 365 Excel.

A fórmula adiciona muitos espaços extras entre as palavras, em seguida, usando o meio, percorremos as palavras que chegam naquele espaço branco extra e recortamos cada parte.

Testamos se a palavra termina em uma vírgula e a próxima termina em dois pontos, depois retorna a palavra menos a vírgula.

Isso tem uma falha em que ele retornará cada palavra seguida por uma vírgula, em que a próxima palavra terminará em dois pontos.