跳到主要内容

如何将一个单元格中的文本和数字分成两列?

现在,如果有一列由文本和数字组成的文本字符串,您希望将文本和数字从一个单元格分离为两个不同的单元格,如下面的屏幕快照所示。 在Excel中,您可以使用以下方法完成此任务。


方法1:用Excel中的公式分隔文本和数字

使用以下公式,您可以将文本和数字从单元格提取到两个单独的单元格中。 请执行以下操作:

1。 将此公式输入到要放置结果的空白单元格C3中: =左(A3,MIN(查找(0,1,2,3,4,5,6,7,8,9 {},A3&"0123456789"))-1)A3 是包含要分隔的文本字符串的单元格,然后按 输入 键以仅从单元格A2中获取文本。 看截图:

doc分割文字编号2

2.然后,您可以通过应用以下公式从单元格中提取数字: =右(A3,LEN(A3)-MIN(查找(0,1,2,3,4,5,6,7,8,9 {},A3&"0123456789"))+1),( A3 是包含要分隔的文本字符串的单元格),将此公式输入到要放置数字的单元格D3中,然后按 输入 密钥,那么您将获得如下数字:

doc分割文字编号3

3。 然后选择单元格C3:D3,然后将填充手柄拖到要包含这些公式的单元格上,您会看到文本和数字已分为不同的单元格:

doc分割文字编号4


将文本字符串拆分或分离为单独的文本和数字列:

Kutools for Excel's 分裂细胞 该功能是一个功能强大的工具,它可以帮助您将单元格值拆分为多列或多行,还可以帮助您将字母数字字符串拆分为单独的文本和数字列等。 点击下载Kutools for Excel!

doc分割文字编号16

方法2:在Excel 2013和更高版本中使用Flash Fill分隔文本和数字

使用上述公式,如果文本在数字之前,则可以将文本和数字分开。 要分隔文本数字之前的数字,您可以使用 Flash填充 Excel 2013和更高版本的功能。

如果您拥有Excel 2013及更高版本, Flash填充 该功能可能会帮助您在一个列中填充文本,在另一列中填充数字,请按照以下步骤操作:

1。 将第一个文本字符串的数字完全输入到相邻的空白单元格-B3中,请参见屏幕截图:

doc分割文字编号5

2。 然后选择要填充数字的区域B3:B7,然后单击 时间 > Flash填充,并且一次只在单元格中填写了数字,请参见屏幕截图:

doc分割文字编号6

3。 然后将文本字符串完全输入到单元格C3中,请参见屏幕截图:

doc分割文字编号7

4. 并选择要仅填充文本的单元格区域C3:C7,单击 时间 > Flash填充 以及第2步,您可以看到,文本已如下分离:

doc分割文字编号8

Tips::您还可以将填充的手柄拖到要使用的范围,然后单击 自动填充选项 并检查 Flash填充.

doc分割文字编号9

方法3:用用户定义的功能将不规则混合的文本和数字分开

如果您有一些文本字符串不规则地混合了文本和数字(如显示的以下数据),则Excel不支持通用功能来解决此问题,但是,您可以创建用户定义函数来完成此操作。

doc分割文字编号17

1。 按住 ALT + F11 键打开 Microsoft Visual Basic for Applications窗口.

2。 点击 插页 > 模块,然后将以下代码粘贴到 模块窗口.

VBA代码:将文本和数字从一个单元格分成不同的单元格

Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
'Updateby Extendoffice
Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
    xStr = VBA.Mid(pWorkRng.Value, i, 1)
    If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
        SplitText = SplitText + xStr
    End If
Next
End Function

3。 然后保存并关闭此代码,返回工作表,输入此公式 = SplitText(A3,FALSE) 放入空白单元格以获取唯一的文本字符串,然后将填充手柄向下拖动到要填充此公式的单元格,请参见屏幕截图:

doc分割文字编号10

4。 然后输入公式 = SplitText(A3,TRUE) 到另一个单元格中,然后将填充手柄向下拖动到要填充此公式以获取数字的单元格,请参见屏幕截图:

doc分割文字编号11

备注:如果文本字符串中有十进制数字,则结果将不正确。


方法 4: 使用 Kutools for Excel 将文本和数字分成两列

如果你有 Kutools for Excel,其功能强大的工具- 分裂细胞 实用程序,您可以将文本字符串快速分为两列:一是数字,另一是文本。

安装后 Kutools for Excel,请执行以下操作:

1。 选择要分隔文本和数字的数据范围。

2。 然后点击 库工具 > 合并与拆分 > 分裂细胞,请参见屏幕截图:

3。 在 分裂细胞 对话框中选择 拆分为列 选项下 Type 部分,然后检查 文字和数字 来自 分割为 部分,请参见屏幕截图:

doc分割文字编号13

4。 然后点击 Ok 按钮,然后会弹出一个提示框,提醒您选择一个单元格以输出结果,请参见屏幕截图:

doc分割文字编号14

5。 点击 OK 按钮,所选内容中的文本字符串已分为两列,如以下屏幕截图所示:

doc分割文字编号15

单击立即下载 Kutools for Excel 并免费试用!


使用 Kutools for Excel 分隔文本和数字

Kutools for Excel:具有300多个方便的Excel加载项,可以在30天内免费试用,没有任何限制。 立即下载并免费试用!

相关文章:

如何在Excel中将单词拆分为单独的字母?

如何通过回车将单元格分为多列或多行?


 

  • Kutools for Excel:免费试用 300 多个 Excel 方便功能

    它为Excel收集了300多种智能专业工具,可将各种复杂任务简化为您在日常工作中的几次单击,可节省大量时间并提高生产率。

    • 将多个工作表或工作簿合并为一个工作簿或工作表
    • 根据背景,字体或条件格式颜色对单元格进行计数和求和
    • 将相同的电子邮件分别发送到带有不同附件的收件人列表
    • 110,000多个Excel用户的选择。 全功能30天免费试用,无需信用卡!
    • 60天退款保证!
    库工具 1 kutools加
  • 将多个工作表或工作簿合并为一个工作表或工作簿

    在日常工作中,将多个工作表或工作簿合并为一张工作表或工作簿可能是一项艰巨的工作,您需要将它们一个接一个地合并。 但是,随着 Kutools for Excel's 结合 功能,只需单击几下即可解决此任务。

    合并表
  • 根据背景,字体或条件格式颜色对单元格进行计数和求和

    根据背景,字体或条件格式颜色在大范围内对单元格值进行计数或求和可能是一项艰巨的任务, Kutools for Excel 支持简单的工具-按颜色计数 这可以帮助您在没有任何Excel技能的情况下尽快处理此工作。

    按颜色计数
  • 将相同的电子邮件分别发送到带有不同附件的收件人列表

    您可以应用Word应用程序将个性化电子邮件发送给没有附件的多个人,但是,使用 Kutools for Excel's 发电子邮件 功能,这种补救措施可以纠正。 同时,您也可以抄送或密送给特定人员的邮件。

    发电子邮件

 

Comments (34)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Tenho uma coluna com vários endereços e os números de endereços. Conforme tabela abaixo. Como separo em uma coluna apenas os nomes e outra coluna apenas os números?

endereço
Avenida Angélica 1235
Rua José Maria Lisboa 456
Rua Celso de Azevedo Marques 307
Rua Rio Duas Barras 953
This comment was minimized by the moderator on the site
Hello, Mateus,
To extract the address number only from the address, the folloiwng formula may help you:
=SUMPRODUCT(MID(0&B3, LARGE(INDEX(ISNUMBER(--MID(B3, ROW(INDIRECT("1:"&LEN(B3))), 1)) * ROW(INDIRECT("1:"&LEN(B3))), 0), ROW(INDIRECT("1:"&LEN(B3))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(B3)))/10)

Please have a try, hope it can help you!
This comment was minimized by the moderator on the site
Bom dia.
Consegui extrair o número, muito obrigado!
Mas como eu consigo extrair agora apenas o endereço?
This comment was minimized by the moderator on the site
Hello, Mateus,
If you want to extract both address and address number, you can apply the Method 3 in this article:

https://www.extendoffice.com/documents/excel/2701-excel-separate-text-and-numbers.html#a3

After copying and pasting the code, please apply the below formulas:
Extract address: =SplitText(B2,FALSE)

Extract address number: =SplitText(B2,TRUE)
Please try, hope it can help you! If you have any other problem, please comment here.
This comment was minimized by the moderator on the site
Agile (11/20/2017 12:00:00 AM)How can I separate this cell into just "Agile" and "(11/20/2017 12:00:00 AM)"
This comment was minimized by the moderator on the site
Hello, Milner,To get the name text, please use this formula: =TRIM(LEFT(A1, FIND("(", A1)-1))To get the date time, please apply this formula: =MID(A1,SEARCH("(",A1),SEARCH(")",A1)-SEARCH("(",A1)+1)Please try the formulas, hope them can help you!
This comment was minimized by the moderator on the site
0002786961 TRAK CDFA #: 0008787942 2722 2723 4536841 N/A 4345784 001018809~00077480

Above is an example of data line I need to split these into 3 types: First: starts with 2 and is of 4 digit (2722 in above example) Second: starts with 2 and is of 7 digit third; starts with 4 is of 7 digit.
I tried separating everything into different columns and then putting if and conditions that I mentioned above but the problem is not everything is getting separated and splitting everything is not efficient enough. I am not able to figure out a vba code for something that satisfies all the conditions and works too.
Can anyone help me out?
This comment was minimized by the moderator on the site
Hello, menze
Do you need to split this three parts 2722 2723 4536841 from the long data? If so, you just can use the Text to Column feature in Excel to split the content into multiple cells by space, then delete the extra data and only keep the data you need.
If not, please upload your Excel file or screenshot here for a reference.
Thank you!
This comment was minimized by the moderator on the site
I have a requirement code that I need to separate out from the text, looks like this 3.1.1.2. Line-of-sight Range Requirements (T=O) The vehicle and units shall be capable of line-of-sight connectivity to all nodes or within a 10-mile radius, whichever is greater. In one column I need the code and the other I need the text. Can you help?
This comment was minimized by the moderator on the site
I have address like 12,anna street,98413256789 i want to seperate address and contact number(mobile or landline) two columns. Is it possible.Pls help to solve
This comment was minimized by the moderator on the site
Hi, Mohan,To seperate address and contact number, please apply the below formulas:Address: =LEFT(A1,LEN(A1)-12)Mobile number: =RIGHT(A1,11)
Please try, hope it can help you!
This comment was minimized by the moderator on the site
Please, help me with the VBA code required to take out "INC000010542805" out of "User KSmith Audit ID INC000010542805 Comment None Control Data".
I have 1,000 rows of this type of data with different number of characters but always with that "INC" string.
This comment was minimized by the moderator on the site
Hello, Kenny,
May be the below formula can solve your problem, please try:
=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("INC",A1),LEN(A1))," ",REPT(" ",100)),100))
This comment was minimized by the moderator on the site
skyyang thanks very much, it works. I really appreciate your help
This comment was minimized by the moderator on the site
how to separate number and letters (1122AB). I tried the upper formula but its not working with me. anybody help me in this regard. Thanks in advance
This comment was minimized by the moderator on the site
Hello, Naeem,
The above formula only works if the text is before the numbers, your numbers before the text, so i recommend the second and third method for you!
Please try, hope it can help you!
This comment was minimized by the moderator on the site
kereeeennn... berhasil....
This comment was minimized by the moderator on the site
Thanks worked for Alpha-numeric cell data [ =RIGHT(A2,LEN(A2)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)]
This comment was minimized by the moderator on the site
Hello Can anyone help me? I want to remove this zero and braceket by an excel formula.
1 BR PE-0.50CT(H SI2)- -0.00( )-2 SP PE-0.50CT(H SI1)-1 RU PE-0.40CT(H-SI)-750GF-RG-RING-25-40-2.50GM


Gowtam
This comment was minimized by the moderator on the site
VERY USEFUL FORMULA I LIKE IT.
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