Comment puis-je effectuer une recherche de chaîne inverse dans Excel sans utiliser VBA?
J'ai une feuille de calcul Excel contenant une liste de chaînes. Chaque chaîne est composée de plusieurs mots, mais le nombre de mots dans chaque chaîne est différente.
En utilisant des fonctions Excel intégrées (pas de VBA), existe-t-il un moyen d'isoler le dernier mot dans chaque chaîne?
Exemples:
Are you classified as human? -> human? Negative, I am a meat popsicle -> popsicle Aziz! Light! -> Light!
14 réponses
Celui - ci est testé et fonctionne (basé sur le post original de Brad):
=RIGHT(A1,LEN(A1)-FIND("|",SUBSTITUTE(A1," ","|",
LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
Si vos chaînes d'origine peuvent contenir un caractère"|", remplacez les deux dans ce qui précède par un autre caractère qui n'apparaîtra pas dans votre source. (Je soupçonne que L'original de Brad a été cassé parce qu'un caractère non imprimable a été supprimé dans la traduction).
Bonus: Comment ça marche (de droite à gauche):
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
– nombre de places dans la chaîne d'origineSUBSTITUTE(A1," ","|", ... )
– Remplace juste le finale de l'espace avec un |
FIND("|", ... )
– Trouve la position absolue de l'remplacé |
(c'était la dernière de l'espace)Right(A1,LEN(A1) - ... ))
– Renvoie tous les caractères après que l' |
EDIT: pour tenir compte du cas où le texte source ne contient pas d'espaces, ajoutez ce qui suit Au début de la formule:
=IF(ISERROR(FIND(" ",A1)),A1, ... )
Faire la formule entière maintenant:
=IF(ISERROR(FIND(" ",A1)),A1, RIGHT(A1,LEN(A1) - FIND("|",
SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))
Ou vous pouvez utiliser la syntaxe =IF(COUNTIF(A1,"* *")
de l'autre version.
Lorsque la chaîne d'origine contenir un espace à la dernière position ajouter une fonction trim tout en comptant tous les espaces: rendre la fonction suivante:
=IF(ISERROR(FIND(" ",B2)),B2, RIGHT(B2,LEN(B2) - FIND("|",
SUBSTITUTE(B2," ","|",LEN(TRIM(B2))-LEN(SUBSTITUTE(B2," ",""))))))
C'est la technique que j'ai utilisée avec beaucoup de succès:
=TRIM(RIGHT(SUBSTITUTE(A1, " ", REPT(" ", 100)), 100))
Pour obtenir le premier mot d'une chaîne, il suffit de changer de droite à gauche
=TRIM(LEFT(SUBSTITUTE(A1, " ", REPT(" ", 100)), 100))
Remplacez également A1 par la cellule contenant le texte.
Une version plus robuste de la réponse de Jerry:
=TRIM(RIGHT(SUBSTITUTE(TRIM(A1), " ", REPT(" ", LEN(TRIM(A1)))), LEN(TRIM(A1))))
Cela fonctionne indépendamment de la longueur de la chaîne, des espaces de début ou de fin, ou de toute autre chose et c'est toujours assez court et simple.
J'ai trouvé ce sur google, testé dans Excel 2003 et ça fonctionne pour moi:
=IF(COUNTIF(A1,"* *"),RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(" ",A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1))))),A1)
[edit] Je n'ai pas assez de représentants pour commenter, donc cela semble le meilleur endroit...La réponse de BradC ne fonctionne pas non plus avec les espaces de fin ou les cellules vides...
[2ème édition] en fait, cela ne fonctionne pas non plus pour les mots simples...
=RIGHT(TRIM(A1),LEN(TRIM(A1))-FIND(CHAR(7),SUBSTITUTE(" "&TRIM(A1)," ",CHAR(7),
LEN(TRIM(A1))-LEN(SUBSTITUTE(" "&TRIM(A1)," ",""))+1))+1)
C'est très robuste-cela fonctionne pour les phrases sans espaces, espaces de début/fin, espaces multiples, espaces de début/fin multiples... et j'ai utilisé char (7) pour le délimiteur plutôt que la barre verticale "|" juste au cas où c'est un élément de texte désiré.
C'est très propre et compact, et fonctionne bien.
{=RIGHT(A1,LEN(A1)-MAX(IF(MID(A1,ROW(1:999),1)=" ",ROW(1:999),0)))}
Il ne piège pas les erreurs pour aucun espace ou un mot, mais c'est facile à ajouter.
Modifier:
Cela gère les espaces de fin, le mot unique et les scénarios de cellules vides. Je n'ai pas trouvé un moyen de le casser.
{=RIGHT(TRIM(A1),LEN(TRIM(A1))-MAX(IF(MID(TRIM(A1),ROW($1:$999),1)=" ",ROW($1:$999),0)))}
=RIGHT(A1,LEN(A1)-FIND("`*`",SUBSTITUTE(A1," ","`*`",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
Pour ajouter aux réponses de Jerry et Joe, si vous voulez trouver le texte avant le dernier mot, vous pouvez utiliser:
=TRIM(LEFT(SUBSTITUTE(TRIM(A1), " ", REPT(" ", LEN(TRIM(A1)))), LEN(SUBSTITUTE(TRIM(A1), " ", REPT(" ", LEN(TRIM(A1)))))-LEN(TRIM(A1))))
Avec 'mon petit chat' dans A1 entraînerait 'mon petit' (où Joe et Jerry donneraient 'chat'
De la même manière que Jerry et Joe isolent le dernier mot, cela obtient alors tout à gauche de cela (puis le redresse)
Imaginez que la chaîne puisse être inversée. Alors c'est vraiment facile. Au lieu de travailler sur la chaîne:
"My little cat" (1)
Vous travaillez avec
"tac elttil yM" (2)
Avec =LEFT(A1;FIND(" ";A1)-1)
A2 vous obtenez "My"
, avec (1) et "tac"
, avec (2), qui est inversé "cat"
, le dernier mot dans (1).
Il y a quelques VBA pour inverser une chaîne. Je préfère la fonction VBA publique ReverseString
.
Installez ce qui précède comme décrit. Ensuite, avec votre chaîne dans A1, par exemple, "My little cat"
et cette fonction dans A2:
=ReverseString(LEFT(ReverseString(A1);IF(ISERROR(FIND(" ";A1));
LEN(A1);(FIND(" ";ReverseString(A1))-1))))
, Vous verrez "cat"
dans A2.
La méthode ci-dessus suppose que les mots sont séparés par des blancs. La clause IF
est pour les cellules contenant des mots simples = aucun blanc dans la cellule. Remarque: TRIM
et CLEAN
la chaîne d'origine sont utiles aussi bien. En principe, il inverse toute la chaîne de A1 et trouve simplement le premier vide dans la chaîne inversée qui est à côté du dernier mot (inversé) (c'est-à-dire "tac "
). LEFT
choisit ce mot et une autre inversion de chaîne reconstitue le ordre original du mot (" cat"
). Le -1
à la fin de l'instruction FIND
supprime le vide.
, L'idée est qu'il est facile d'extraire la première(!) mot dans une chaîne avec LEFT
et FIND
dans le premier vide. Cependant, pour la dernière(!) word la fonction RIGHT
est le mauvais choix lorsque vous essayez de le faire car malheureusement FIND n'a pas de drapeau pour la direction que vous voulez analyser votre chaîne.
Par conséquent, toute la chaîne est simplement inversée. LEFT
et FIND
fonctionne normalement mais la chaîne extraite est inversée. Mais son est pas une grosse affaire une fois que vous savez comment inverser une chaîne. La première instruction ReverseString
dans la formule fait ce travail.
=LEFT(A1,FIND(IF(
ISERROR(
FIND("_",A1)
),A1,RIGHT(A1,
LEN(A1)-FIND("~",
SUBSTITUTE(A1,"_","~",
LEN(A1)-LEN(SUBSTITUTE(A1,"_",""))
)
)
)
),A1,1)-2)
J'ai traduit en PT-BR, car j'en avais besoin aussi.
(Veuillez noter que j'ai changé l'espace en \
parce que j'avais besoin du nom de fichier UNIQUEMENT des chaînes de chemin.)
=SE(ÉERRO(PROCURAR("\",A1)),A1,DIREITA(A1,NÚM.CARACT(A1)-PROCURAR("|", SUBSTITUIR(A1,"\","|",NÚM.CARACT(A1)-NÚM.CARACT(SUBSTITUIR(A1,"\",""))))))
Une Autre façon d'y parvenir est comme ci-dessous
=IF(ISERROR(TRIM(MID(TRIM(D14),SEARCH("|",SUBSTITUTE(TRIM(D14)," ","|",LEN(TRIM(D14))-LEN(SUBSTITUTE(TRIM(D14)," ","")))),LEN(TRIM(D14))))),TRIM(D14),TRIM(MID(TRIM(D14),SEARCH("|",SUBSTITUTE(TRIM(D14)," ","|",LEN(TRIM(D14))-LEN(SUBSTITUTE(TRIM(D14)," ","")))),LEN(TRIM(D14)))))
Copiez dans une colonne, sélectionnez cette colonne et accueil > Édition > Rechercher et sélectionner, remplacer:
Remplacer Tous Les.
Il y a un espace après l'astérisque.
J'ai aussi eu une tâche comme celle-ci et quand j'ai été fait, en utilisant la méthode ci-dessus, une nouvelle méthode s'est produite pour moi: pourquoi ne pas faire ceci:
- inverse la chaîne ("string one" devient "ENO gnirts").
- Utilisez la bonne vieille recherche (qui est codée en dur pour de gauche à droite).
- inversez - le en chaîne lisible à nouveau.
Comment ça sonne?