提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

如何更改或將數字轉換為Excel中的文本?

有時候您可能不希望單元格中的數字參與計算,或者您可能想要在單元格中顯示數字中的前導零。 為此,您可能需要將數字更改為文字。 以下方法可以將單元格中的數字轉換為Excel中的文本。

使用文本功能將數字轉換為文本

使用格式單元格命令將數字轉換為文本

使用Kutools for Excel將數字轉換為文本


將數字轉換或拼寫為英文或中文貨幣:

Kutools for Excel's 拼出數字 功能,可以將數字拼寫成Excel中的英文和中文單詞。例如,它可以拼出“12.75“ 至 ”十二美元七十五美分".

doc將數字轉換為文本10

Kutools for Excel:比300方便的Excel加載項,可以在60天免費試用。 下載並免費試用現在!


使用文本功能將數字轉換為文本

如果您熟悉Microsoft Excel的公式,則可以使用Text函數將單元格中的數字轉換為文本。

如果您只想將數字轉換為文本而不使用任何格式,則可以使用以下公式: = TEXT(A1“0”);

1。 在單元格E1中,請輸入公式 = TEXT(A1“0”)。

doc將數字轉換為文本1

2。 然後按 輸入 鍵。 並選擇單元格E1,將填充手柄拖過要應用此公式的單元格範圍。請參見截圖:

doc將數字轉換為文本2

備註:由於數據是公式,您可以將公式更改為值。 複製單元格並右鍵單擊要粘貼值並選擇的位置 粘貼特殊值>值。

如果你想在數字中顯示前導零,你可以使用這個公式: = TEXT(A1“00000”); 如 將23轉換為00023.

根據上述步驟,如果應用此公式,您將得到以下結果:

doc將數字轉換為文本3

如果您想將數字轉換為保留幾位小數的文本,則可以使用以下公式: = TEXT(A1“0.000”)。 如 將23轉換為23.000.

同樣通過以上步驟,如果您應用此公式,您將得到如下截圖所示的結果:

doc將數字轉換為文本4

備註:您可以定義文字樣式。 例如,如果您要將電話號碼轉換為電話號碼格式的文本,則可以將“000”替換為“00-00000”或其他。


辦公室標籤圖片

裁員賽季即將到來,仍然緩慢運作?
-- Office Tab 提高您的步伐,節省50%的工作時間!

  • 驚人! 多個文檔的操作比單個文檔更加輕鬆和方便;
  • 與其他Web瀏覽器相比,Office Tab的界面更加強大和美觀;
  • 減少成千上萬的繁瑣鼠標點擊,告別頸椎病和老鼠手;
  • 被90,000精英和300 +知名公司選中!
全功能,免費試用30天 更多詳情 現在就下載!

使用格式單元格命令將數字轉換為文本

Microsoft Excel的 單元格格式 命令也能夠將數字轉換為文本。

1。 選擇您想要轉換為文本的數字。

2。 右鍵單擊所選範圍,然後選擇 格式單元格從上下文菜單中選擇一項。 看截圖:

doc將數字轉換為文本5

3。 在 單元格格式 對話框中選擇 文本 項目中 類別 盒子下 選項卡,然後單擊 OK 按鈕。 看截圖:

doc將數字轉換為文本6

4。 然後,所選範圍中的數字將轉換為文本。


使用Kutools for Excel將數字轉換為文本

如果你有 Kutools for Excel 安裝後,您將更容易將單元格中的數字轉換為文本 在文本和數字之間進行轉換.

Kutools for Excel : 與超過300方便的Excel加載項,在60天免費試用沒有限制.

安裝後 Kutools for Excel請按照以下步驟操作:

1。 選擇您想要更改為文字的範圍。

2。 點擊 Kutools > 內容 > 在文本和數字之間進行轉換。 看截圖:

doc將數字轉換為文本7

3。 在 在文本和數字之間進行轉換 對話框,檢查 數字到文本 選項,然後單擊 OK or 應用 按鈕。 所有的數字已經被轉換為原始範圍的文本。 看截圖:

doc將數字轉換為文本8

點擊下載Kutools for Excel和免費試用版吧!


Kutools for Excel:比200方便的Excel加載項,可以在60天免費試用。 下載並免費試用現在!


相關文章:

如何更改或將文本轉換為Excel中的數字?


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.
    Eduardo Levy · 17 days ago
    I would like to convert the date 201901 in word " january¨ or number 01 (means january in numbers) in excel sheet. Thank you
  • To post as a guest, your comment is unpublished.
    Alice · 7 months ago
    How do you convert to text an amount with decimal number of which the decimal numbers remain as numbers? example: 750.25 - SEVEN HUNDRED FIFTY & 25/100.. Thank you
  • To post as a guest, your comment is unpublished.
    Anton Thelander · 8 months ago
    Large thanks!
  • To post as a guest, your comment is unpublished.
    Tere Martin Rangel · 9 months ago
    Cuando convierto un numero en texto me lo pone en Ingles y en Dólares, lo necesito es en Español y en pesos Mexicanos, ¿como puedo resolver esto?
    De antemano gracias por la atención que se sirva darme.
  • To post as a guest, your comment is unpublished.
    mg · 9 months ago
    Hi, not working in our currency which is philippine peso. Kindly help. Thank you.
  • To post as a guest, your comment is unpublished.
    Praveen · 1 years ago
    After converting words to figures, How can start with "Rupees" and end with "Only"
  • To post as a guest, your comment is unpublished.
    MNB · 1 years ago
    If i have assign
    0 as Q
    1 as W
    2 as R
    3 as E
    4 as Z
    5 as S
    6 as T
    7 as B
    8 as K
    9 as A
    how to convert a numerical value to text code like 2356 i want 2 as R, 3 as E, 5 as S, 6 as T, so when i write 2356 i got result REST?
    • To post as a guest, your comment is unpublished.
      Ata Biabani · 11 months ago
      A workaround can be using the SUBSTITUTE formula ten times to replace all the numbers with the respective letters:

      =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,"Q"),1,"W"),2,"R"),3,"E"),4,"Z"),5,"S"),6,"T"),7,"B"),8,"K"),9,"A")

      Where A1 is the reference cell for the numeric values.
  • To post as a guest, your comment is unpublished.
    Ramesh · 1 years ago
    When I entered Numbers in Cell Eg : 150 the will written as One hundred and fifty
  • To post as a guest, your comment is unpublished.
    mary · 1 years ago
    how translate staff id to text as "filled" and vice versa for nil staff ID to 'vacant"
  • To post as a guest, your comment is unpublished.
    Sikhar Chakravarty · 1 years ago
    i m a novice. text in cells read as 1234 5678 1234 i.e with a space bet each 4 digit clusters. how can i compress it as 123456781234 ?
    • To post as a guest, your comment is unpublished.
      Ata Biabani · 1 years ago
      =SUBSTITUTE(A1," ","")
      Where A1 is reference cell for 1234 5678 1234
      • To post as a guest, your comment is unpublished.
        Stéphane Degrémont · 11 months ago
        Great tip thanks. Now FYI this didn't work at first for me, as for some reason it didn't recognize the space.. but when I cut and pasted the "space" from the source (cut-and-pasted financial values from a web page) instead of pressing the space bar it worked fine.
        • To post as a guest, your comment is unpublished.
          Ata Biabani · 11 months ago
          It might be another Character that appears like the Space character. When you copy and paste data from external sources such as websites, unwanted characters will come across with different ASCII codes that may look like the Space character.

          The above formula meant to remove only the Space character from your string. Space character means the value of the Char(32) on the ASCII values.

          Best would be to modify the formula as =SUBSTITUTE(A1,CHAR(32),"")
    • To post as a guest, your comment is unpublished.
      Vinay Prajapati · 1 years ago
      Try Using =CONCATENATE("",LEFT(H20,4),MID(H20,6,4),RIGHT(H20,4))


      Whereis H20 is reference cell for 1234 5678 1234
      • To post as a guest, your comment is unpublished.
        Anonymous · 1 years ago
        Use replace function. Highlight the cells you want to edit then Ctrl+H. Replace space to blank
  • To post as a guest, your comment is unpublished.
    kamlesh manchekar · 1 years ago
    I want to convert digit into words in indian language? How to do that?
    • To post as a guest, your comment is unpublished.
      Vinay Prajapati · 1 years ago
      Try Using This -

      =IF(MID(K14,1,1)="1","One",IF(MID(K14,1,1)="2","Two",IF(MID(K14,1,1)="3","Three",IF(MID(K14,1,1)="4","Four",IF(MID(K14,1,1)="5","Five",IF(MID(K14,1,1)="6","Six",IF(MID(K14,1,1)="7","Seven",IF(MID(K14,1,1)="8","Eight",IF(MID(K14,1,1)="9","Nine",IF(MID(K14,1,1)="0","Zero",""))))))))))&" "&IF(RIGHT(K14,1)="1","One",IF(RIGHT(K14,1)="2","Two",IF(RIGHT(K14,1)="3","Three",IF(RIGHT(K14,1)="4","Four",IF(RIGHT(K14,1)="5","Five",IF(RIGHT(K14,1)="6","Six",IF(RIGHT(K14,1)="7","Seven",IF(RIGHT(K14,1)="8","Eight",IF(RIGHT(K14,1)="9","Nine",IF(RIGHT(K14,1)="0","Zero",""))))))))))


      Instead of Using "One" "Two" "Three", You can change what ever you want against perticular No, also above code is for only upto two digits. you can extended it to any no by adding IF with MID formula.


      K14 is reference cell for Number.
      • To post as a guest, your comment is unpublished.
        Darshan · 1 years ago
        Using this formula, 99 would convert into 'Nine Nine'. Same goes for every other number (two digited). Seems the question was to how to spell a numerical value.
  • To post as a guest, your comment is unpublished.
    Amrita · 2 years ago
    I couldn't covert number into million and billion $ currency. Help me to because I am confused.
  • To post as a guest, your comment is unpublished.
    Amrita · 2 years ago
    I want to convert dollar currency into million or billion format . I am confused do it. could you plz guide me.
  • To post as a guest, your comment is unpublished.
    Shafi · 2 years ago
    Some clarification, when I try to convert below number it changes its format, eg. given below can you explain why this happen? other than zero it works fine

    B64=890151200020,
    H64=0
    J64=TEXT(B64,"000 0000 0000 0 "&H64) it changes to "089 0151 2000 2 0" instead of "890 1512 0002 0 0"
  • To post as a guest, your comment is unpublished.
    Excel Expert · 2 years ago
    Perfect solution has been found.
    Full formulas could not be written due to space limitation. mail me at mukeshbrahmankar(gmail) for the file. File would be sent on a friendly note, no commercial thing.

    Regards
  • To post as a guest, your comment is unpublished.
    Alexandra · 2 years ago
    Any chace t change date in Text format, not keeping Date format?
    Ex. 1/1/2017 d/m/yyyy in the same way but text formatting?
  • To post as a guest, your comment is unpublished.
    FALSE · 2 years ago
    tnx for your solution
    be healthy
  • To post as a guest, your comment is unpublished.
    John Smith · 2 years ago
    I cannot reproduce your result. When using the TEXT formula trying to convert a number to text, the resulting content was left-aligned but there was no green triangle, meaning the content was still a number, not text. Same result when using the menu command. No triangle.

    I use Excel 2010.
  • To post as a guest, your comment is unpublished.
    John Smith · 2 years ago
    I cannot reproduce your results. When I used the first method trying to convert a number to text, the resulting number was left-aligned but there was not green triangle, meaning the content was still a number, not text. Same result with using the menu command. No green triangle.

    I'm using Excel 2010.
  • To post as a guest, your comment is unpublished.
    Imans · 2 years ago
    [quote name="Rajesh K"]Hi., Using conditional formatting you can do this. Select the range, goto conditional formatting (Cells that contain value), enter the value as per your requirement. Now select format goto Number - Custom and then type the text you are looking for within quotes. You can get your result. It will be useful for visual purpose only. Though the result displayed is in Text format, if you click on the cell it will have original value what it had. Try it....[/quote]

    :lol: horray great it will save time and brain ! thanks
  • To post as a guest, your comment is unpublished.
    Benjamin Jackson · 2 years ago
    Hi there,
    i would like to convert this numner to words in excel
    For example : 12,500.44 to USD twelve thousand five hundred and cents forty four only. Please give excel in add-ins format
  • To post as a guest, your comment is unpublished.
    CHOW · 2 years ago
    Hi there,
    i would like to convert this numner to words in excel
    For example : 14,444.44 to FOURTEEN THOUSAND FOUR HUNDRED FORTY FOUR AND CENTS FORTY FOUR ONLY
  • To post as a guest, your comment is unpublished.
    Sami Awad · 2 years ago
    You can easily do that, fist lets say that the number is in the cell F44, type this formula in the cell you want to display the number in text :
    =SpellNumber(F44)

    lets say that you have a number in cell c23 , and you wand to display it ni c 24, then you will type in c24 the following: =SpellNumber(C23)
  • To post as a guest, your comment is unpublished.
    HANY · 2 years ago
    I already made an excel sheet that does this, please send me your email address and i will forward it to you, i cannot find any way to attach it here for the common benefit.
  • To post as a guest, your comment is unpublished.
    mas · 3 years ago
    how we can change a cell that contain for example [2"] ( size of pipe) to pure number 2
  • To post as a guest, your comment is unpublished.
    mahendran · 3 years ago
    dear sir
    i want typing the number in excel then next convert to words

    sample-1000-One thousand
  • To post as a guest, your comment is unpublished.
    Mike · 3 years ago
    Thank you! I needed to know how to convert numbers to zero-filled text.
  • To post as a guest, your comment is unpublished.
    sajid · 3 years ago
    plz send me formula step wise of the convert number into text .
    how can i convert a number into text
  • To post as a guest, your comment is unpublished.
    Balaji Panchal · 3 years ago
    Dear sir
    I am Balaji
    I need to convert number to text like as below e.g.
  • To post as a guest, your comment is unpublished.
    Balaji Panchal · 3 years ago
    Dear sir
    I want a number to text convert for that help me sir which is function in excel
  • To post as a guest, your comment is unpublished.
    surjan · 4 years ago
    I want to change number into words


    eg: 1000 in words one thousand only
  • To post as a guest, your comment is unpublished.
    surjan · 4 years ago
    Dear Provider,
    I need to convert number to text like as below e.g.
    344835.10 = THREE HUNDRED FORTY FOUR THOUSAND EIGHT HUNDRED THIRTY FIVE & TEN PAISA ONLY.
  • To post as a guest, your comment is unpublished.
    KALANITHI · 4 years ago
    I want to change number into words


    eg: 1000 in words one thousand only
  • To post as a guest, your comment is unpublished.
    KALANITHI · 4 years ago
    Sir ,
    How to change number in words in exel sheets

    Example :

    100 = one hundred
  • To post as a guest, your comment is unpublished.
    Naveen Gowda · 4 years ago
    Dear Sir

    I want to change number into words


    eg: 1000 in words one thousand only
  • To post as a guest, your comment is unpublished.
    Naveen Gowda · 4 years ago
    [wiki]12[/wiki][quote]12[/quote][url]1[/url]
  • To post as a guest, your comment is unpublished.
    Dawn · 4 years ago
    WRONG. "Convert number to text with Format Cells command" does not actually change the number into text, it only changes the cell formatting. Which means it looks like text but may be treated like a number. A number changed this way will not be recognized as text by the COUNTIF function, although it will be by the COUNTA function. It's confusing even to experienced users, and Microsoft has never made the distinction clear.
  • To post as a guest, your comment is unpublished.
    Abu · 4 years ago
    I want automatic convert into inwards in excell sheet.
  • To post as a guest, your comment is unpublished.
    arun prakash · 5 years ago
    HOW TO CONVERT THE Numeric into Azeri words ? plz help ,Can you send the spellnumber VBA PROGRAM FOR THAT////Plz help
  • To post as a guest, your comment is unpublished.
    ashish Nautiyal · 5 years ago
    Dear All,

    Please help me about convert number to word

    20= Twenty
  • To post as a guest, your comment is unpublished.
    Vinay Puri · 5 years ago
    I Want to change number to text

    example: -500 in words
  • To post as a guest, your comment is unpublished.
    Rajkumar · 5 years ago
    sir

    I want to change number into words


    example: 1000 in words one thousand only
  • To post as a guest, your comment is unpublished.
    Ivan · 5 years ago
    Use the IF Function ex: 100 One Hundred [b]=IF(I7=100,"One Hundred") [/b] Thanks,
  • To post as a guest, your comment is unpublished.
    connie · 5 years ago
    Hello, I am needing to find out how I would convert this into a formula?

    I want to input a formula in cell f5 that takes
    d5 divided by e5 then multiple the answer by 100 and this would be my answer in cell f5
    Example D5 is 95 and E5 is 110
    95/110=0.86364x100=86.364 which if possible would like to round the 86.364 to 86 because this is the percent. 86%

    Could you please help because I can not figure this formula out! Thank you ...
    • To post as a guest, your comment is unpublished.
      Ashwin Singh · 5 years ago
      Hi connie,

      please try this formula.. may be it will be helpful for you. =ROUND(D5/E5*100,0)
    • To post as a guest, your comment is unpublished.
      Rajesh K · 5 years ago
      Hi., Using conditional formatting you can do this. Select the range, goto conditional formatting (Cells that contain value), enter the value as per your requirement. Now select format goto Number - Custom and then type the text you are looking for within quotes. You can get your result. It will be useful for visual purpose only. Though the result displayed is in Text format, if you click on the cell it will have original value what it had. Try it....
  • To post as a guest, your comment is unpublished.
    Stan · 5 years ago
    Does anybody know how to convert a number to a word when the value of that number reaches a specific point? Example: if a cell is higher than 10 I want that number to be replaced with a word, so my clients cant see my inventory. THANK U SO MUCH FOR UR HELP
  • To post as a guest, your comment is unpublished.
    AINIE · 5 years ago
    #Ainie

    Dear Provider,
    I need to convert number to text like as below e.g.
    344835.10 = THREE HUNDRED FORTY FOUR THOUSAND EIGHT HUNDRED THIRTY FIVE & TEN PAISA ONLY.


    if you can help us it's better for my commercial work.[/quote]
    • To post as a guest, your comment is unpublished.
      Devrao Pawar · 5 years ago
      [quote name="

      Dear Provider,
      I need to convert number to text like as below e.g.
      344835.10 = THREE HUNDRED FORTY FOUR THOUSAND EIGHT HUNDRED THIRTY FIVE & TEN PAISA ONLY.


      if you can help us it's better for my commercial work.[/quote][/quote]
  • To post as a guest, your comment is unpublished.
    Md. Faruk Hossain · 5 years ago
    I want convert 100 to in word One Hundred. Please tell me how way I could convert.
  • To post as a guest, your comment is unpublished.
    Girraj mehta · 5 years ago
    Dear Provider,
    I need to convert number to text like as below e.g.
    1000=ONE THOUSAND ONLy
  • To post as a guest, your comment is unpublished.
    Loknath Sapkota · 5 years ago
    Dear Provider,
    I need to convert number to text like as below e.g.
    344835.10 = THREE HUNDRED FORTY FOUR THOUSAND EIGHT HUNDRED THIRTY FIVE & TEN PAISA ONLY.


    if you can help us it's better for my commercial work.
  • To post as a guest, your comment is unpublished.
    Maya Academy Urimari · 5 years ago
    change number into figure in Excel
    like 2 = Two

    Step1: press alt+F11
    Step2: Insert à Module

    Step3: type ='wordnum(12)' will show `Twelve'.



    Option Explicit
    Public Numbers As Variant, Tens As Variant

    Sub SetNums()
    Numbers = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
    Tens = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
    End Sub

    Function WordNum(MyNumber As Double) As String
    Dim DecimalPosition As Integer, ValNo As Variant, StrNo As String
    Dim NumStr As String, n As Integer, Temp1 As String, Temp2 As String
    ' This macro was written by Chris Mead - www.MeadInKent.co.uk

    If Abs(MyNumber) > 999999999 Then
    WordNum = "Value too large"
    Exit Function
    End If

    SetNums

    ' String representation of amount (excl decimals)
    NumStr = Right("000000000" & Trim(Str(Int(Abs(MyNumber)))), 9)
    ValNo = Array(0, Val(Mid(NumStr, 1, 3)), Val(Mid(NumStr, 4, 3)), Val(Mid(NumStr, 7, 3)))

    For n = 3 To 1 Step -1 'analyse the absolute number as 3 sets of 3 digits
    StrNo = Format(ValNo(n), "000")

    If ValNo(n) > 0 Then
    Temp1 = GetTens(Val(Right(StrNo, 2)))
    If Left(StrNo, 1) "0" Then
    Temp2 = Numbers(Val(Left(StrNo, 1))) & " hundred"
    If Temp1 "" Then Temp2 = Temp2 & " and "
    Else
    Temp2 = ""
    End If

    If n = 3 Then
    If Temp2 = "" And ValNo(1) + ValNo(2) > 0 Then Temp2 = "and "
    WordNum = Trim(Temp2 & Temp1)
    End If
    • To post as a guest, your comment is unpublished.
      calix · 3 years ago
      this part here is not okay:

      If Left(StrNo, 1) "0" Then
      Temp2 = Numbers(Val(Lef t(StrNo, 1))) & " hundred"
      If Temp1 "" Then Temp2 = Temp2 & " and "

      how to correct that please? thanks
    • To post as a guest, your comment is unpublished.
      Sparky · 5 years ago
      There seems to be some errors in the above code - doesn't work quite right (for example, result when tested with "1" is "hundred and".
      • To post as a guest, your comment is unpublished.
        Ravi Shankar S · 6 days ago
        This is for All! Any body wishing to get your numbers to text (like in words) I have already created a VB some 12 years ago which used to work perfectly as I was doing some Financial projects that period of time....
        Any body interested to transform the code to your excel can send me a mail on R-aviang@live.com. I will share the code. Thanks
        Ravi Shankar S