Wie kann man in einer Zelle Zeichen von rechts nach links ziehen oder extrahieren, bis in Excel ein Leerzeichen erreicht ist?
In diesem Artikel wird das Ziehen oder Extrahieren von Zeichen von rechts in einer Zelle behandelt, bis ein Leerzeichen erreicht ist, um das folgende Ergebnis im Excel-Arbeitsblatt zu erhalten. Eine nützliche Formel in diesem Artikel kann diesen Job schnell und einfach lösen.
Ziehen oder extrahieren Sie Zeichen von rechts in eine Zelle, bis ein Leerzeichen mit der Formel erreicht ist
Hier ist eine einfache Formel, die Ihnen helfen kann, die Zeichen von rechts aus der Zelle zu extrahieren, bis ein Leerzeichen erreicht ist. Gehen Sie dazu wie folgt vor:
Geben Sie diese Formel ein: = TRIM (RECHTS (ERSATZ (A2, "", REPT ("", 255)), 255)) in eine leere Zelle, in der Sie das Ergebnis erhalten möchten, und ziehen Sie dann den Füllpunkt nach unten zu den Zellen, in die Sie diese Formel füllen möchten. Alle Zeichen von rechts in den Zellen, wenn Sie das erste Leerzeichen treffen, werden sofort extrahiert (siehe Abbildung):
This comment was minimized by the moderator on the site
si, solo tienes que cambiar el numero en negrella a 2 de la formula "=EXTRAE(A5;ENCONTRAR("@";SUSTITUIR(A5;" ";"@";LARGO(A5)-LARGO(SUSTITUIR(A5;" ";""))-1))+1;100)"
This comment was minimized by the moderator on the site
Hi, thanks so much for the help! Is there any way to do the same thing, but to pull out text from right to left until the THIRD space? It would save my life!!!!
This comment was minimized by the moderator on the site
I am using a similar formula =TRIM(RIGHT(SUBSTITUTE(F7," ",REPT(" ",255)),255)) which is copying the word to G7, however I would like it to remove the word from F7. Are you able to assist me with what I need to adjust in my formula?
This comment was minimized by the moderator on the site
This is a great formula. I would love to understand the individual parts a bit better. Could someone provide a bit of a summary as to what each part is doing?
TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),255))
This comment was minimized by the moderator on the site
Explanation of TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),255))
TLDR: pad the space before the last character(s) on the right with 255 spaces, extract 255 characters from the right, then trim the space characters, leaving the desired non-text characters.
Long explanation:
1. REPT (Repeat) is adding 255 space characters. WHY? These added spaces will be inserted by SUBSTITUE.
2. SUBSTITUTE is replacing each space characters with 255 spaces (provided by REPT). WHY? This causes a cell to contain the desired characters on the right, plus 255 space characters to the left of the desired cell characters; this will be exploited by the RIGHT extraction function.
3. RIGHT is extracting the first 255 characters from the right. WHY? This extracts the desired characters on the right plus the added space characters (up to 255 characters) inserted by SUBSTITUTE.
4. TRIM is removing all space characters. WHY? This leaves only the desired characters on the right.
Caveat: This formula only works so long as the desired right text characters are <=255 characters. If the desired right text characters are >256, then the RIGHT function will miss the characters over 255.
This comment was minimized by the moderator on the site
Is there a way to extract and the result be read as a number? I am extracting the number part of an exported column that excel is reading as text. Thanks!
This comment was minimized by the moderator on the site
Hello, Russell,
If you need the result as number format, you should copy and paste the formula cells into other cells as values, and then convert the text format number to real number.
This comment was minimized by the moderator on the site
Hi, Paulius,
To extract the text from right to left until the second space, you should apply the below formula:
=MID(A2,FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1))+1,100)
This comment was minimized by the moderator on the site
This is close to what I need help with so I'm hoping someone can help. I need to pull over 20 characters or until the next space. how can I modify the formulas above to do that?
