提示:其他語言是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。 支持所有語言。 在您的企業或組織中輕鬆部署。 全功能30天免費試用。
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.
    David Bidzhoian · 18 days ago
    Hi. I wonder to know is it possible to extract from the next string '102-105+106-10605-10605 -10631-10632-10633-10634-10635+107' all values of only three digits that have sign '-' (i.e. minus) before them (or plus - doesn't matter). Then extract extract all 5-digits values with the same rule? If this is possible to do I will save many many hours of manual work that I can devote to lot's of uselful tasks.

    P.S.
    I know that regular expressions are able to do that, BUT... I hate their syntax, so I'm searching any other method to complete this task.

    Thanks in advance.
  • To post as a guest, your comment is unpublished.
    Ala' Marridi · 21 days ago
    Hi, is it possible to extract the numbers before "g", for example : 25,20,15,25,300,40

    Ali Baba Dark Chocolate 25g box 12 pcs
    Ali Baba Dark Chocolate 20g*24 box
    Cadbury 5 Star White Chocolate 15g
    Kinder 2 White Chocolate 25g*24
    ALpella Biscuits W/Marshmallow300g
    Alpella Chocolate 40g
  • To post as a guest, your comment is unpublished.
    Satyaa1 · 21 days ago
    Hi, is it possible to extract the numbers before "g", for example : 25,20,15,25,300,40


    Ali Baba Dark Chocolate 25g box 12 pcs
    Ali Baba Dark Chocolate 20g*24 box
    Cadbury 5 Star White Chocolate 15g
    Kinder 2 White Chocolate 25g*24
    ALpella Biscuits W/Marshmallow300g
    Alpella Chocolate 40g
  • To post as a guest, your comment is unpublished.
    Veras · 4 months ago
    Tkssssss you saved my day :) God bless you
  • To post as a guest, your comment is unpublished.
    Alex · 5 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 · 6 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 · 7 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 · 7 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 · 10 months ago
    This isn't working for decimals, it's removing decimal point.
  • To post as a guest, your comment is unpublished.
    josia · 1 years 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 · 4 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 · 1 years 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 · 1 years 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 · 2 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 · 2 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 · 2 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 · 2 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 · 2 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 · 3 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 · 4 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 · 3 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 · 5 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 · 5 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 · 5 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 · 5 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 .