Як витягнути або витягти символи справа наліво в комірці, поки в Excel не буде досягнуто пробілу?
У цій статті мова піде про витягування або витягування символів праворуч у комірці, доки не буде досягнуто місця для отримання наступного результату на аркуші Excel. Корисна формула в цій статті може швидко і легко вирішити цю роботу.
Витягніть або витягніть символи праворуч у комірці, доки не буде досягнуто пробілу за допомогою формули
Ось проста формула, яка може допомогти вам витягнути символи праворуч з комірки, поки не буде вичерпано пробіл, зробіть так:
Введіть цю формулу: = TRIM (ПРАВО (ЗАМІНИТИ (A2, "", REPT ("", 255)), 255)) в порожню комірку, де ви хочете отримати результат, а потім перетягніть маркер заповнення вниз до комірок, якими ви хочете заповнити цю формулу, і всі символи праворуч у клітинках при зустрічі з першим пробілом витягуються одночасно, див. знімок екрана:
Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу. Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...
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?
