跳到主要内容

如何在单元格中从右到左提取或提取字符，直到在Excel中达到空格？

本文将讨论从右边的单元格中拉出或提取字符，直到到达空格以在Excel工作表中获得以下结果为止。 本文中的一个有用公式可以快速轻松地解决此问题。

从单元格的右侧提取或提取字符，直到公式达到空格为止

从单元格的右侧提取或提取字符，直到公式达到空格为止

这是一个简单的公式，可以帮助您从单元格中提取字符，直到遇到空格为止，请按照以下步骤操作：

输入以下公式： = TRIM（RIGHT（SUBSTITUTE（A2，“”，REPT（“”，255）），255）） 到要获取结果的空白单元格中，然后将填充手柄向下拖动到要填充此公式的单元格中，并在遇到第一个空格时立即提取单元格中所有从右到右的字符，请参见截图：

最佳办公生产力工具

🤖 Kutools 人工智能助手：基于以下内容彻底改变数据分析： 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup  |   多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧   |  工作簿和工作表管理器   |  资源库 （自动文本）   |  日期选择器   |  合并工作表   |  加密/解密单元格   |  按列表发送电子邮件   |  超级筛选   |   特殊过滤器 （过滤粗体/斜体/删除线...）...
前 15 个工具集:  12 文本 工具 (添加文本, 删除字符，...）   |   50+ 图表 类型 (甘特图，...）   |   40+ 实用 公式 (根据生日计算年龄，...）   |   19 插入 工具 (插入二维码, 从路径插入图片，...）   |   12 转化 工具 (小写金额转大写, 货币兑换，...）   |   7 合并与拆分 工具 (高级组合行, 分裂细胞，...）   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能，体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

产品描述

Office Tab 为 Office 带来选项卡式界面，让您的工作更加轻松

  • 在Word，Excel，PowerPoint中启用选项卡式编辑和阅读，发布者，Access，Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50％，并减少数百次鼠标单击！
Comments (17)
No ratings yet. Be the first to rate!
Mahendra Deshmukh
Mahendra Deshmukh
This comment was minimized by the moderator on the site
This is a nice piece of formula,
I took liberty to change it and it worked for me.

Considering your data between two spaces is not more than 20 characters and you need data between spaces try this.

Original Formula for last space to end 
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),255))


Between 2nd last space and last space
=TRIM(LEFT(RIGHT(SUBSTITUTE(C1," ",REPT(" ",25)),50),25))


Between 3rd last space and 2nd last space

=TRIM(LEFT(RIGHT(SUBSTITUTE(C1," ",REPT(" ",25)),50),25))
Reply
0
0
Seadog
Seadog
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)"
Reply
0
0
victor santos
victor santos
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!!!!
Reply
0
0
skyyang
skyyang     victor santos
This comment was minimized by the moderator on the site
Hello, santos
To extract the text from right to left until the THIRD space, please apply the below formula:
=IF((LEN(A1)-LEN(SUBSTITUTE(A1," ","")))<3, A1, RIGHT(A1,LEN(A1)-FIND("/", SUBSTITUTE(A1," ","/", (LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-2)))))


Please have a try, hope it can help you!
Reply
Report
0
0
_sbird
_sbird
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?
Reply
Report
0
0
Matt
Matt
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))
Reply
0
0
Jason Howarth
Jason Howarth     Matt
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.
Reply
0
0
Stuart Bass
Stuart Bass
This comment was minimized by the moderator on the site
Hi,
I had to put a Trim(A2) into mine as I had trailing blanks - otherwise works great.
Reply
0
0
russell_w
russell_w
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!
Reply
Report
0
0
skyyang
skyyang     russell_w
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.
View Attachments (1 / 5)
Reply
Report
0
0
chethiya
chethiya
This comment was minimized by the moderator on the site
thanks a lot
Reply
0
0
Paulius
Paulius
This comment was minimized by the moderator on the site
This formula worked perfectly:
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),255))

Is there any way to do the same thing, but to pullout text from right to left untill the SECOND space?
Reply
0
0
skyyang
skyyang     Paulius
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)

Please try, hope it can help you!
View Attachments (1 / 5)
Reply
Report
0
0
DEREK
DEREK     skyyang
This comment was minimized by the moderator on the site
Thank you!!!!!!!!!!
Reply
0
0
Paulius
Paulius     skyyang
This comment was minimized by the moderator on the site
Thank You it worked perfectly
Reply
0
0
Rifat Tareq
Rifat Tareq
This comment was minimized by the moderator on the site
Thank you so much! Saved me a ton of time!
Reply
0
0
JH
JH     Rifat Tareq
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?
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations