提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

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

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


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

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

doc split text number 16
Office选项卡在Office中启用选项卡式编辑和浏览,使您的工作更轻松......
Kutools for Excel - 最佳办公生产力工具将解决您的大部分Excel问题
  • 重用任何东西: 将最常用或最复杂的公式,图表和其他任何内容添加到您的收藏夹中,并在将来快速重复使用它们。
  • 超过20文本功能: 从文本字符串中提取数字; 提取或删除部分文本; 将数字和货币转换为英语单词...
  • 合并工具:多个工作簿和表格合二为一; 合并多个单元格/行/列而不丢失数据; 合并重复行和总和...
  • 拆分工具:根据价值将数据拆分为多个表格; 一个工作簿到多个Excel,PDF或CSV文件; 一列到多列......
  • 粘贴跳过 隐藏/过滤行; 数和总和 按背景颜色; 创建邮件列表和 通过Cell的价值发送电子邮件...
  • 超级过滤器: 创建高级过滤方案并应用于任何工作表; 排序 按周,日,频率等; 筛选 通过大胆,公式,评论......
  • 超过300强大的功能; 适用于Office 2007-2019和365; 支持所有语言; 在公司轻松部署; 全功能60天免费试用。

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

使用以下公式,可以将单元格中的文本和数字提取到两个分离的单元格中。 请做如下操作:

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

doc split text number 2

2。 然后你可以通过应用这个公式从单元格中提取数字: =RIGHT(A3,LEN(A3)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A3&"0123456789"))+1),( A3 是包含要分离的文本字符串的单元格),请将此公式输入到要放置数字的单元格D3中,然后按 输入 键,那么你会得到这样的数字:

doc split text number 3

3。 然后选择单元格C3:D3,然后将填充句柄拖到要包含这些公式的单元格中,并且可以看到文本和数字已被分隔到不同的单元格中:

doc split text number 4


方法2:使用Flash分隔文本和数字填写Excel 2013及更高版本

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

如果您有Excel 2013及更高版本,则 Flash填充 功能可以帮助您填写一列中的文本和另一列中的数字,请执行以下操作:

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

doc split text number 5

2。 然后选择您想填充数字的范围B3:B7,然后单击 数据 > Flash填充,并且只有数字一次填入细胞中,请参见截图:

doc split text number 6

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

doc split text number 7

4. 然后选择单元格范围C3:C7,您只需填写文本,单击 数据 > Flash填充 以及步骤2,你可以看到,文本已被分隔如下:

doc split text number 8

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

doc split text number 9

方法3:使用用户定义函数分隔不规则混合的文本和数字

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

doc split text number 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 split text number 10

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

doc split text number 11

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


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

如果你有 Kutools for Excel凭借其强大的工具 - 拆分单元格 实用程序,您可以快速将文本字符串分成两列:一个是数字,另一个是文本。

安装后 Kutools for Excel请按照以下步骤进行:

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

2。 然后点击 Kutools > 文本 > 拆分单元格,看截图:

doc split text number 12

3。 在 拆分单元格 对话框中选择 拆分为列 选项下 类型 部分,然后检查 文本和数字 来自 拆分 部分,看截图:

doc split text number 13

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

doc split text number 14

5。 点击 OK 按钮,并且选择中的文本字符串被分为两列,如下面的屏幕截图所示:

doc split text number 15

点击下载Kutools for Excel和免费试用版吧!


用Kutools for Excel分隔文本和数字

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!


相关文章:

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

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


Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Naeem Zafar · 3 months ago
    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
    • To post as a guest, your comment is unpublished.
      skyyang · 3 months ago
      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!
  • To post as a guest, your comment is unpublished.
    Hay · 7 months ago
    kereeeennn... berhasil....
  • To post as a guest, your comment is unpublished.
    AM · 9 months ago
    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)]
  • To post as a guest, your comment is unpublished.
    Gowtam Roopun · 10 months ago
    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
  • To post as a guest, your comment is unpublished.
    RAJ · 10 months ago
    VERY USEFUL FORMULA I LIKE IT.
  • To post as a guest, your comment is unpublished.
    Sarashwaty · 1 years ago
    Hi.... Thank you for sharing, it is very helpful and save lots of time for me.
    Regards,
    Saras.
  • To post as a guest, your comment is unpublished.
    mahsa s · 1 years ago
    Hi,
    I'm trying to split these values HarryJack 22 3,66 335,77 44,77 into two columns which I can only have text in one column and 22 in the second column.
  • To post as a guest, your comment is unpublished.
    yuvraj · 1 years ago
    11247, 11322, 11323, 11324, 11325, 11326, 11332, 11337
    11247, 11322, 11323, 11324, 11325, 11326, 11332, 11337
    11247, 11322, 11323, 11324, 11325, 11326, 11332, 11337
    11248, 11249, 11250, 11322, 11323, 11324, 11325, 11326, 11332, 11337
    11248, 11322, 11323, 11324, 11325, 11326, 11332, 11337 how to separate above numbers in different cell with same number
    • To post as a guest, your comment is unpublished.
      S · 9 months ago
      Text to coloum with , separated format.
  • To post as a guest, your comment is unpublished.
    sagar chutiya · 1 years ago
    laure ka baal ,madarjaat
    • To post as a guest, your comment is unpublished.
      Baba · 1 months ago
      Mind your language sagar ch*tiya...

      This is a professional site and professional members.


      If your don't control your abusive and vulgar language then I will just cut-off your tongue.


      Thank You!!!
  • To post as a guest, your comment is unpublished.
    Bhavesh Desai · 1 years ago
    Super useful. Thank you very much. You saved my time!
  • To post as a guest, your comment is unpublished.
    vemky · 2 years ago
    20161021-014340_3125013233_OUTUS_agent012-all.mp3


    how to sperate this all in diffrent colums
  • To post as a guest, your comment is unpublished.
    Raju · 2 years ago
    can you help my in this question (SEONI-MALWA734274SEONIMALWA ) i want to split only number by formula . I tried many time but i split only 734274SEONIMALWA
    so if you have any formula then help me
  • To post as a guest, your comment is unpublished.
    AARON GABRIEL · 2 years ago
    Hey,
    I want to split this cell which contains (TI_122006001550)
    Now my aim is to omit the Everything else on that and keep only 122006
    Please help me generate a formula for this
    Would be great
    Thanks
  • To post as a guest, your comment is unpublished.
    shaker · 2 years ago
    Thanks for your valuable info.if it is possible can plz explain it briefly.
    This is the data like which i have in E column i want only number whether its starting or middle or last i want numeric number.can you provide code this kind of data
    Appreciate your help
    [b]BILL ID :AHM CLG 150236 SBI
    REDDY AGENCIES \HDF \000349
    DINAJPUR BEEJ \AXI \055313[/b]



    Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
    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
  • To post as a guest, your comment is unpublished.
    sunny · 3 years ago
    thanks a lot. was very helpful. saved lots of time