跳到主要内容

如何在Excel中提取两个不同字符之间的字符串?

如果您在Excel中有一个字符串列表,需要从下面的屏幕截图中提取两个字符之间的字符串部分,那么如何尽快处理它? 在这里,我介绍一些解决这项工作的方法。

使用公式提取两个不同字符之间的零件字符串

用公式提取两个相同字符之间的部分字符串

使用 Kutools for Excel 提取两个字符之间的部分字符串好主意3


使用公式提取两个不同字符之间的零件字符串

要提取两个不同字符之间的部分字符串,可以执行以下操作:

选择要放置结果的单元格,键入此公式 =MID(LEFT(A1,FIND(">",A1)-1),FIND("<",A1)+1,LEN(A1)),然后按 Enter 键。
doc提取两个字符之间的字符串1

备注:A1是文本单元格, >< 是您要提取其间字符串的两个字符。


用公式提取两个相同字符之间的部分字符串

如果要提取两个相同字符之间的部分字符串,可以执行以下操作:

选择要放置结果的单元格,键入此公式 =SUBSTITUTE(MID(SUBSTITUTE("/" & A3&REPT(" ",6),"/",REPT(",",255)),2*255,255),",",""),然后按 Enter 键。
doc提取两个字符之间的字符串2

请注意: A3是文本单元格, / 是您要提取的字符。

使用 Kutools for Excel 提取两个字符之间的部分字符串

如果你有 Kutools for Excel,您还可以提取两个文本之间的零件字符串。

Kutools for Excel, 与超过 300 方便的功能,使您的工作更加轻松。 

安装后 Kutools for Excel,请执行以下操作:(立即免费下载Kutools for Excel!)

1.选择一个将放置提取的字符串的单元格,然后单击 库工具 > 公式 > 公式助手.
doc提取两个字符之间的字符串3

2.在 公式助手 对话框,.check 筛选器 复选框,然后键入 “前” 在文本框中,有关提取的所有公式将列在 选择一个公式 部分中,选择 提取指定文本之间的字符串,然后向右走 参数输入 部分中,选择要从中提取子字符串的单元格 手机,然后输入您要提取的两个文本。
doc kutools提取两个文本之间的字符串2

3。 点击 Ok,然后已提取您指定的两个文本之间的子字符串,向下拖动填充手柄以从下面的每个单元格提取子字符串。
doc kutools提取两个文本之间的字符串3

doc kutools提取两个文本之间的字符串4

最佳办公生产力工具

🤖 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 (35)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Bonjour,

je souhaite extraire de ce texte (USD) 934.915765 (30-11-2022) le numero 934.915765, ce que je souhaite c'est faire un extrait de tout ce qui est entre (USD) et (30-11-2022)
This comment was minimized by the moderator on the site
Hi, please try this formula:
=LEFT((MID(I1,FIND(" ",I1)+1,256)),FIND(" ",(MID(I1,FIND(" ",I1)+1,256)),1)-1)
I1 is the cell that you want to extract number.
This comment was minimized by the moderator on the site
Hi, try this formula please:
=IFERROR(MID(A1,FIND("CG",A1),11),IFERROR(MID(A1,FIND("CP",A1),11),IFERROR(MID(A1,FIND("CV",A1),11),IFERROR(MID(A1,FIND("CD",A1),11),IFERROR(MID(A1,FIND("CC",A1),11),MID(A1,FIND("CO",A1),11))))))

If there is no match string, it returns #VALUE! error.
This comment was minimized by the moderator on the site
Hello, can you please help me with extracting a code from a cell? The code is always 11 characters long and it can start with either CC, CP, CV, CO, CG or CD. If it helps, it also ends with a number always. The code does not have a fixed position, it can be in the beginning, middle or end.
I tried this formula =MID(A2,FIND("CP",A2),11) but, as you see, it only finds the ones starting with CP.

An example of what the cell might contain is:
Text text CG1234dh201 text 123 text

Thank you!
This comment was minimized by the moderator on the site
Hi, I have an issue which i am struggling to solve. So I have a number of answers and i want to separate into separate cells.

i.e. A: XYZ|B*: SDR|C: AQS|

I can separate but my issue is there loads of line with the asterisk moving to the correct answer for each question. How do i get my head around this?

I am currently using the below formula

=MID(A5,SEARCH("A:",cell ref)+2,SEARCH("|",A5)-SEARCH(":",cell ref)-1)
This comment was minimized by the moderator on the site
Hi, Mohannmed Faisal, from my understanding, your question is to extract XYZ, SDR and AQS separately into three cells. If so, please use formulas below separately:
=MID(A7,SEARCH("A:",A7)+3,3)
=MID(A7,SEARCH("B*:",A7)+4,3)
=MID(A7,SEARCH("|C:",A7)+4,3)
A7 is the cell that you use to extract.
Hope it help you.
This comment was minimized by the moderator on the site
Bonjour,

J'ai un texte AAMMJJ que je souhaite transformer en JJMMAAAA
This comment was minimized by the moderator on the site
Hi, GG, try this formula =TEXTE(date; "jj/mm/aaaa")
This comment was minimized by the moderator on the site
Hi How to get specific value from description like
Desc: Date: 2022-07-22 23:59:51 Node: VA10TWPSQL026 Type: TSM SM_SERVER_EVENT 2579 Resource: 1700 Message Key: TSM_VA10TWPSQL026

I want to get node value as VA10TWPSQL026
This comment was minimized by the moderator on the site
Hi, Sandhya, if the node value always has a fixed length (14-chars), you can try this formula: =MID(A1,SEARCH("Node",A1)+5,14) hope it do a favor for you.
This comment was minimized by the moderator on the site
Hola,

Me encantan tus foros.

Me podrias ayudar con esto?

Tengo una serie de columnas asi:

U CALIPER R/H/R 1J0615424H Es
N DOOR WINDOW SWITCH R/H/F 8E0959851D5PR Ebbett Audi
N TAILIGHT L/H - LENS & BODY - ON QTR - W/XENON H/LAMP 63217217311 Coombes Johnson European Ltd
U*GUARD R/H/F - REPLACE RET NUT ALSO 5G0821106A (V) Private Purchase

Lo que necesito extraer es el numero de parte de cada celda pero siempre esta en una posicion diferente, por ejemplo de la primer celda necesito solo 1J0615424H, de la segunda necesito extraer 8E0959851D5PR, de la tercera 63217217311 y de la ultima 5G0821106A

Muchas gracias

Anderson
This comment was minimized by the moderator on the site
Hallo,

Ik moet een stuk tekst extraheren uit een cel, maar kom er niet aan uit.

51420647 Ondersteuner Kringdirectie (Unfilled)
P_00040501 Productmanager Wendbare Pool Bedrijven (Unfilled)

De tekst die ik nodig heb ik is het middelste deel tussen de cijfers en (unfilled).
Welke formuler kan ik hiervoor gebruiken?
This comment was minimized by the moderator on the site
HelloI have an issue where I am trying to extract a number from a set of text and numbers in excel. This is an excerpt from the data I have:AUS23-7W
GER490-8W
MEX114-2M
MEX444-5M
MEX331-3W
US118-15W
ARG572-16W
Japan122-6W
Japan526-13W
ARG585-7W
Japan398-16W
Japan320-15W
ARG141-3Mo
Canada329-5W
US421-17Mo
Canada427-14W
I need to be able to extract just the number to the right of the dash. For example, I need Japan320-15W to return just the 15 in its own column. Is there a formula you could provide me with that can accomplish this? Many thanks!

This comment was minimized by the moderator on the site
Hello, Duggled, to solve your job, just need two steps. Firstly, select the text strings, and apply Text to Columns under Data tab, choose Delimited > Other, and type - into the textbox beside Other option, click Finish. In this step, your data has been split into two columns by deliliter -. Secondly, use the formula  =SUMPRODUCT(MID(0&B1, LARGE(INDEX(ISNUMBER(--MID(B1, ROW(INDIRECT("1:"&LEN(B1))), 1)) * ROW(INDIRECT("1:"&LEN(B1))), 0), ROW(INDIRECT("1:"&LEN(B1))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(B1)))/10)(A1 is the cell that you want to extract the number from, here you change it to your second column cell)then the numbers after - have been extracted. Or in the second step, you can apply VBA, or our handy tool, for more details, please visit:https://www.extendoffice.com/documents/excel/1622-excel-extract-number-from-string.html
This comment was minimized by the moderator on the site
This formula works well for pretty much everything except these data :MEX114-2M
MEX444-5M
MEX373-15M
MEX540-8M
MEX396-11M
MEX386-10M
MEX369-14M
MEX446-3M
MEX75-12M
MEX372-3M
MEX178-6M
MEX510-7M
MEX361-17M
MEX501-13M
MEX221-17M
Is there any way you could revise that formula to include these? Thank you so much!
This comment was minimized by the moderator on the site
Hi! Sorry if I reposted this and thank you so much for the formula.
But I'm not sure how to do this, I have this in a cell:

random comment;24/nov/21 2:34 PM;random ID;3240

and I need to get in separate cells the date without the hour, like 24/nov/21 . And, in another cell, the last numer; in this case 3240 (The format is alway the same, and the date is always 9 characters long, the only thing that might change is the last number, as is the number of seconds it took to do something)

How should I do it?
Thank you!! 
This comment was minimized by the moderator on the site
Hi, to extract date, please use formula =MID(A1,16,9), A1 is the cell that the original data places, to extract ID number, please use formula =MID(A1,44,10),, for more details, you can refer to this articel https://www.extendoffice.com/documents/excel/3639-excel-extract-part-of-string.html
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations