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

or

如何从Excel中的文本字符串中提取数字?


仅从文本字符串中提取数字:

您订购的 Kutools for Excel EXTRACTNUMBERS 功能,您可以快速从文本字符串单元格中提取数字。

doc仅提取数字14

下载并免费试用60-day


方法1:仅从带有公式的文本字符串中提取数字


以下长公式可以帮助您仅从文本字符串中提取数字,请执行以下操作:

选择要输出提取的数字的空白单元格,然后键入以下公式: = SUMPRODUCT(MID(0&A5,LARGE(INDEX(ISNUMBER( - MID(A5,ROW(INDIRECT(“1:”&LEN(A5))),1))* ROW(间接(“1:”&LEN(A5) )),0),ROW(间接(“1:”&LEN(A5))))+ 1,1)* 10 ^ ROW(间接(“1:”&LEN(A5)))/ 10),然后拖动填充手柄以填充应用此公式所需的范围。 看截图:

doc仅提取数字2

备注:

  • 1. A5 站在您要从列表中提取数字的第一个数据。
  • 2。 当字符串中没有数字时,结果将显示为0。

方法2:仅从具有VBA代码的文本字符串中提取数字

这是一个VBA代码也可以帮到你,请按以下步骤操作:

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

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

VBA代码:仅从文本字符串中提取数字:

Sub ExtrNumbersFromRange()
    Dim xRg As Range
    Dim xDRg As Range
    Dim xRRg As Range
    Dim nCellLength As Integer
    Dim xNumber As Integer
    Dim strNumber As String
    Dim xTitleId As String
    Dim xI As Integer
    xTitleId = "KutoolsforExcel"
    Set xDRg = Application.InputBox("Please select text strings:", xTitleId, "", Type:=8)
    If TypeName(xDRg) = "Nothing" Then Exit Sub
    Set xRRg = Application.InputBox("Please select output cell:", xTitleId, "", Type:=8)
    If TypeName(xRRg) = "Nothing" Then Exit Sub
    xI = 0
    strNumber = ""
  For Each xRg In xDRg
    xI = xI + 1
    nCellLength = Len(xRg)
    For xNumber = 1 To nCellLength
      If IsNumeric(Mid(xRg, xNumber, 1)) Then
        strNumber = strNumber & Mid(xRg, xNumber, 1)
      End If
    Next xNumber
    xRRg.Item(xI) = strNumber
    strNumber = ""
  Next xRg
End Sub

3。 然后,按 F5 键运行此代码,弹出一个提示框以提醒您选择要使用的文本范围,请参见屏幕截图:

doc仅提取数字3

4。 然后,点击 OK,另一个提示框如下,请选择一个单元格输出结果,见截图:

doc仅提取数字4

5。 最后点击 OK 按钮,并且一次提取所选单元格中的所有数字。


方法3:仅使用Kutools for Excel从文本字符串中提取数字

Kutools for Excel 也有一个被称为强大的功能 EXTRACTNUMBERS,使用此功能,您可以快速从原始文本字符串中提取数字。

Kutools for Excel : 与超过300方便的Excel加载项,在60天免费试用没有限制.

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

1。 单击除文本字符串之外的单元格,您将在其中放置结果,请参阅截图:

doc仅提取数字5

2。 然后点击 Kutools > Kutools功能 > 文本 > EXTRACTNUMBERS,看截图:

doc仅提取数字6

3。 在 函数参数 对话框中,选择一个要从中提取数字的单元格 文本 文本框,然后输入 or N 文本框,看截图:

doc仅提取数字7

注意:争论 N 是一个可选项目,如果你输入 ,如果你输入的话,它会返回数字 ,它将以文本格式返回数字,默认值为false,因此您可以将其保留为空。

4。 然后点击 OK,数字已从选定的单元格中提取,然后将填充手柄向下拖动到要应用此功能的单元格,您将得到以下结果:

doc仅提取数字8

点击下载并免费试用Kutools for Excel Now!


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

如果要将文本字符串拆分为单独的文本和数字列, Kutools for Excel拆分单元格 也可以帮助你解决这个任务。

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

1。 选择要分割的文本字符串,然后单击 Kutools > 文本 > 拆分单元格,看截图:

doc仅提取数字9

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

doc仅提取数字10

3。 然后点击 Ok 按钮,选择一个单元格将结果放入弹出的对话框中,看截图:

doc仅提取数字11

4。 然后点击 OK 按钮,并且文本字符串已被分割为单独的文本和数字列,如下面的屏幕截图所示:

doc仅提取数字12

点击下载并免费试用Kutools for Excel Now!


方法5:仅从带有公式的文本字符串中提取十进制数

如果文本字符串包含工作表中的一些十进制数字,那么如何从文本字符串中仅提取十进制数字?

以下公式可以帮助您快速轻松地从文本字符串中提取十进制数。

输入以下公式:=LOOKUP(9.9E+307,--LEFT(MID(A5,MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $A5&"1023456789")),999),ROW(INDIRECT("1:999")))),然后将句柄填充到要包含此公式的单元格,所有十进制数字都已从文本字符串中提取,请参见屏幕截图:

doc仅提取数字13


仅从使用Kutools for Excel的字符串提取数字

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


相关文章:


Kutools for Excel - 最佳办公生产力工具提高80%的生产力

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
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.
    Veras · 1 months ago
    Tkssssss you saved my day :) God bless you
  • To post as a guest, your comment is unpublished.
    Alex · 2 months ago
    Hi ! You guys are impressive with formulas... I need one to extract the room number of a conference room. The Source Cells looks like AB ABCD CITY-Street-1-004-REST/Pers1 ABCD AB. I need to extract the 004 from it. The city lenght varies, as well as the street name as well as the floor number (this one is 1 but it could be 12). I was trying to find a way to jump to third "-" and take only the follow digits and stop at next alphabetical characters. I don't want to take the numbers of persons the room can have (Pers1)...

    Much appreciated guys!!!!
  • To post as a guest, your comment is unpublished.
    Aaron Freije · 3 months ago
    Hello, I could use help with a formula. I have read through these posts thinking I could find a formula or two that I could pull from to make work but I can't get it just right. I have product descriptions where the last section represents the case count (i.e CS/6 means 6 in a case). I would like a formula to pull out just the case count number. Here are a couple examples: LUCR LACT FR 1 % CALC CHOC 64 OZ CS/6 (need 6 pulled out), PCHP DAIRY CRMR FR VAN 32 OZ CS/12 (need 12 pulled out), and GLEN HALFNHALF ASEP SS GU 3/8 OZ CS/360 (need 360 pulled out).
  • To post as a guest, your comment is unpublished.
    Satya · 4 months ago
    4900 Meridian Street, Normal, Alabama 35762
    445 Health Sciences Boulevard, Dothan, Alabama 36303-2251

    how to separate the State code number which is presented in last
    • To post as a guest, your comment is unpublished.
      skyyang · 4 months ago
      Hi, Satya,
      To extract only the state code number, you should apply the below formula:
      =MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))+1,5)
      If you want to extract all numbers in the last, please use this formula:
      =MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))+1,10)
  • To post as a guest, your comment is unpublished.
    Shahid · 7 months ago
    This isn't working for decimals, it's removing decimal point.
  • To post as a guest, your comment is unpublished.
    josia · 11 months ago
    for example if i have these two strings, how do i extract the numbers that begin with 44133 in cells B1 and B2

    cell A1: "255 water bill payment 44133256487 payment by 255766854254 dsm"

    Cell A2: "255 payment by 255745654875 dsm water bill 44133647851"
    • To post as a guest, your comment is unpublished.
      MOHAMMAD OWAIS · 1 months ago
      first do a find. =FIND(44,A1)
      then mid =MID(A1,FIND(44,A1),11)

      I wish it is helpful.
  • To post as a guest, your comment is unpublished.
    josia · 11 months ago
    hi.if i have a list which has text and Control numbers and phone number all in one cell but each record has different positions for the 3, and i want to extract control numbers(which all begin with say value 44133*****) how best can i extract that information
  • To post as a guest, your comment is unpublished.
    Farhad · 11 months ago
    Hi
    Can any one help me to solve this problem :


    " Hotel to kadamtali: 20/(r).kadamtali to charkhai bazar:90/.(cng).bazar to site:20/(r).site to charkhai bazar:20/.charkhai bazar to sharker bazar:80/.(cng) kanaigath to site:30/(r).site to kanaigath:50/(r).carry matarials).sharkerbazar to syleth : 870/.(cng Reserve,carry one site matarials).kadamtali to zindabazar:40/.zindbazar to amborkhana:20/.amborkhana to companygonj:200/.(cng).companygonj to vholagonj site:30/.vholagonj site to amborkhana:2 seat,400/cng.(carry matarials),amborkhana to upashar:60/(cng).carry matarials.



    Here i want to take the value ( "/") before this. and add them.
  • To post as a guest, your comment is unpublished.
    KA · 1 years ago
    Here's a nasty one I'm stuck on - I want to separately extract both numbers from this cell: Down(+): 3,537.78 Over(+): 1,965.30

    Each number can vary in length, but will always have two digits after the decimal.
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, KA,
      May be the Text to Columns feature in Excel can help you to separate the text and numbers, please see the following screenshot:

      After splitting the cell contents, you just need to delete the text columns and only keep the number columns as you need.
  • To post as a guest, your comment is unpublished.
    Excel Master · 1 years ago
    Thank you. Its great.
  • To post as a guest, your comment is unpublished.
    Matt · 1 years ago
    "Select a blank cell that is adjacent to the list you want to extract number only, and type this formula =SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10) (A2 stands the first data you want to extract numbers only from the list), then press Shift + Ctrl + Enter buttons, and drag the fill handle to fill the range you need to apply this formula"

    It's not necessary to press Shift + Ctrl + Enter. Press only ENTER (sumproduct know to work with array)
    • To post as a guest, your comment is unpublished.
      Melvin Koshy · 1 years ago
      This is absolutely amazing.
      1. Please explain the logic of this formula briefly
      2. Could you explain why we have to press Ctrl+Shift+Enter.
  • To post as a guest, your comment is unpublished.
    andrew · 1 years ago
    Hi,
    i want to extract only the number after the Colon in a cell
    80lb : 12
    90lb : 4
    110lb : 0
    120lb : 20
    130lb : 6
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, andrew,
      To extract the numbers after the colon,pleas apply the below formula:
      =MID(A1,FIND(":",A1)+2,256)
      Please try it, thank you!
  • To post as a guest, your comment is unpublished.
    Rashmita · 1 years ago
    I want to extract pincodes from this list:

    Adikavi Nannaya University, Jaya Krishnapuram, Rajahmundry – 533 105, Andhra Pradesh.
    Andhra University, Visakhapatnam-530 003.
    Acharya Nagarjuna University, Nagarjuna Nagar, Guntur-522 510.
    Dravidian University, Kuppam-517 425.

    Is there anyone can help me on this?
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Rashmita,

      If you just need to extract the number codes from the addresses, may be the Kutools for Excel's Extractnumbers function can help you.
      Please view the screenshot image:
  • To post as a guest, your comment is unpublished.
    vikram · 2 years ago
    How do i get True or false when i compare numbers & text for example
    d1, d2, d3 3 TRUE


    d1, d2, d4, d5 5 FALSE
  • To post as a guest, your comment is unpublished.
    Dexter · 2 years ago
    Hi,
    I would like to know if there is a formula to get the total $ value of this string in Excel (Guy1-$201.6, Guy2-$915.85, Guy3-$495, Guy4-$1211, Guy5-$492) - and extent to 20 'Guys'. I don't want to convert etc ... I want a formula please.
    Thanks
    • To post as a guest, your comment is unpublished.
      Giri · 2 years ago
      Try the formula

      =MID(F14,FIND("$",F14),100)+0
  • To post as a guest, your comment is unpublished.
    Samoil · 2 years ago
    Updated Formula which handles decimal point and works on any number length

    =SUMPRODUCT(MID(0&D2,LARGE(INDEX(ISNUMBER(--MID(D2,ROW(INDIRECT("$1:$"&LEN(D2))),1))*
    ROW(INDIRECT("$1:$"&LEN(D2))),0),ROW(INDIRECT("$1:$"&LEN(D2))))+1,1)*10^ROW(INDIRECT("$1:$"&LEN(D2)))/POWER(10, IFERROR(LEN(D2)-FIND(".", D2)-1, 1)))

    D2 is the cell where you have number in a string format
    Copy this formula to E2 and press CTRL+SHIFT+ENTER
    • To post as a guest, your comment is unpublished.
      umari · 2 years ago
      it doesnt work on below scenario:

      Provident<space>Fund<2spaces>-5.55556%<space>0.00<space>

      Same like this : Provident Fund -5.55556% 0.00
      i want -5.55556% or -0.055556
    • To post as a guest, your comment is unpublished.
      Noeun · 2 years ago
      hi man can you explain how the formula works?
  • To post as a guest, your comment is unpublished.
    adrie farndell · 3 years ago
    Good day
    I would like to extract the first set of numbers from a list. ie (122,90,84,118.4,128.9)
    Any ideas on what formula I can use?
    COIL112X2.5
    COIL90X2.5
    COIL84X2.0
    COIL118.4X1.8
    COIL128.9X2.0
    • To post as a guest, your comment is unpublished.
      Giri · 2 years ago
      =+MID(C14,5,FIND("X",C14)-1-LEN("coil"))
    • To post as a guest, your comment is unpublished.
      Giri · 2 years ago
      Try the formula
      =+MID(C13,5,FIND("X",C13)-1-LEN("coil"))
  • To post as a guest, your comment is unpublished.
    Yusuf Ali · 3 years ago
    I have a huge list of Barcodes with product description which might have weights and other numeric values. I wish to extract only the barcodes which are 8-13 digits in len. Is there any way the above mentioned formulae can be tweeked.
  • To post as a guest, your comment is unpublished.
    adeel · 4 years ago
    thanks, really helpful
  • To post as a guest, your comment is unpublished.
    Deepak · 4 years ago
    I want the Formula that contains number but stop working where next alphabet is start.

    Like: hh123456f2
    gh123f3
    ff1234g1

    I don't want last numerical number after the alphabet.

    Hope you understand what I'm trying
  • To post as a guest, your comment is unpublished.
    salar lotfee · 4 years ago
    perfect, just have wasted a day in the factory by the scanning machine in 38' centigrade standing with no results, but this blew it up. super thanks
  • To post as a guest, your comment is unpublished.
    mohamed elrify · 4 years ago
    I think that this formula will be easier
    =SUM(IFERROR(VALUE(MID(A1;ROW($1:$100);1));0))
    • To post as a guest, your comment is unpublished.
      Deepak Kumar · 4 years ago
      [quote name="mohamed elrify"]I think that this formula will be easier
      =SUM(IFERROR(VALUE(MID(A1;ROW($1:$100);1));0))[/quote]

      What u have posted will some then not extract them!!
  • To post as a guest, your comment is unpublished.
    dEEPAK · 4 years ago
    This will more short..

    =NPV(-0.9,IFERROR(MID(A1,100-ROW($1:$99),1)/10,""))

    with CSE
    • To post as a guest, your comment is unpublished.
      Gopal · 2 years ago
      Great! this works Deepak!

      only one problem - it won't work with the decimals. example, if A1 is 2.25LXX, it will extract it as 225, not 2.25, is there a way in your formula to extract the decimal number also? thanks in advance!
  • To post as a guest, your comment is unpublished.
    dEEPAK · 4 years ago
    How about this... :-)

    [b]=NPV(-0.9,IFERROR(MID(A1,1+LEN(A1)-ROW($1:$999),1)/10,""))
    [/b]
    [i]Press Ctrl + Shift + Enter[/i]
  • To post as a guest, your comment is unpublished.
    xlViki · 4 years ago
    @jb: Use this UDF:


    Function ExtractNumber(cell As Range) As Long
    Dim Num As String

    For i = 1 To Len(cell)
    If IsNumeric(Mid(cell.Value, i, 1)) Then Num = Num & Mid(cell.Value, i, 1)
    Next i

    ExtractNumber = Num

    End Function
  • To post as a guest, your comment is unpublished.
    jb · 4 years ago
    this formula
    =SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

    worked great, for any number under 4 digital but I have numbers over 1000 that I need to extract - any advice??

    thanks :)
  • To post as a guest, your comment is unpublished.
    jb · 4 years ago
    Thank you thank you!

    have been trolling the web for answer to simply extra numbers from string in excel and after many overbearing, confusing formulas yours worked like a treat!!

    much appreciated :D
  • To post as a guest, your comment is unpublished.
    Georgios · 4 years ago
    Great formula...very helpful!
    But, could you please advise what can I do with chinese characters? I guess it considers them as figures. Any idea?
  • To post as a guest, your comment is unpublished.
    Muksharna G · 5 years ago
    This formula fails with decimal places. e.g. "1.5 grams of abc" returns "15".
  • To post as a guest, your comment is unpublished.
    Allen Kelly · 5 years ago
    Christ it was either [b]Multiplan or Quattro Pro that used to[/b] return the string value of a single item with a single command...
  • To post as a guest, your comment is unpublished.
    Mandeep · 5 years ago
    Who can explain this formula to me .