·
5 months ago
great article. can i use the same for unicode such as hebrew (Convert Letter To Number With Formula)
如何在Excel中將字母轉換為數字或反之亦然？
如果你有一個字母表的字母表，現在你想把這些字母轉換成它們的相對數字，例如A = 1，B = 2，C = 3 ...而在其他情況下，你需要將這個選項倒轉將數字更改為相關的字母。 你有沒有試過在Excel工作表中解決這個任務？
Office選項卡在Office中啟用選項卡式編輯和瀏覽，使您的工作更輕鬆......
Kutools for Excel解決了您的大多數問題，並使您的生產率提高了80％
- 重用任何東西： 將最常用或最複雜的公式，圖表和其他任何內容添加到您的收藏夾中，並在將來快速重複使用它們。
- 超過20文本功能： 從文本字符串中提取數字； 提取或刪除部分文字； 將數字和貨幣轉換為英文單詞。
- 合併工具：將多個工作簿和工作表合二為一； 合併多個單元格/行/列，而不會丟失數據； 合併重複的行和總和。
- 拆分工具：根據價值將數據分割成多個工作表； 一本工作簿可轉換為多個Excel，PDF或CSV文件； 一列到多列。
- 粘貼跳過 隱藏/過濾行; 數和總和 按背景顏色; 將個性化電子郵件批量發送給多個收件人。
- 超級過濾器： 創建高級過濾方案並應用於任何工作表; 分類 按週，日，頻率等; 過濾 通過大膽，公式，評論......
- 超過300強大的功能; 與Office 2007-2019和365一起使用； 支持所有語言； 在您的企業或組織中輕鬆部署。
將公式轉換為數字
要將這些字母轉換為相對數字，可以使用以下公式：
1。 在您的數據之外的空白單元格中，例如，單元格D2，請輸入以下公式，然後按 輸入 鍵。
= COLUMN（INDIRECT（B3＆1））
2。 然後將填充手柄拖到要應用此公式的範圍，並將字母轉換為其相對編號，如下所示：
在Excel中輕鬆將多個數字批量轉換為貨幣單詞
手動將貨幣$ 357.6轉換為三百五十七美元六十美分的貨幣單詞很容易。 但如何將數百個數字轉換為貨幣單詞呢？ 使用驚人的 數字到貨幣詞 的特點 Excel的Kutools，並在Excel中輕鬆地將數字轉換為貨幣字。
全功能免費試用30天！
Kutools for Excel -包括多個用於Excel的300便捷工具。 全功能免費試用30天，無需信用卡！ 立即行動吧
將數字轉換為帶有公式的字母
如果你想把1到26範圍內的整數轉換成相應的字母，下面的公式可以幫你一個忙。
1。 在除數據之外的空白單元格中，例如，單元格D3，請輸入以下公式，然後按 輸入 鍵。
= MID（“ABCDEFGHIJKLMNOPQRSTUVWXYZ”，B3,1）
2。 然後將填充手柄拖動到您想要包含此公式的範圍，並根據需要將這些數字轉換為相對字母。 看截圖：
小技巧：
:
（1）如果數字大於26，則公式將返回空;
（2）如果數字小於1，公式將返回#VALUE！ 誤差值;
（3）如果數字是十進制數，則公式將數字修剪為整數，然後計算。
使用一個神奇的工具將數字轉換為貨幣文本
如果已安裝 Kutools for Excel，你可以用它 數字到貨幣詞 功能可將所有選定的數字轉換為Excel中的批量貨幣字。
Kutools for Excel - 包括多個用於Excel的300便捷工具。 全功能免費試用60天，無需信用卡！ 立即行動吧
1。 選擇要轉換為貨幣字的數字，然後單擊 Kutools > 內容 > 數字到貨幣詞.
2。 在Numbers to Currency Words對話框中，請檢查 English or 中文 選項，然後單擊 Ok 按鈕。
現在，您將看到所有選定的數字將批量轉換為貨幣字。 看截圖：
相關文章：
Kutools for Excel解決了您的大多數問題，並使您的生產率提高了80％
- 重用: 快速插入 複雜的公式，圖表 以及你以前用過的任何東西; 加密單元格 密碼; 創建郵件列表 並發送電子郵件...
- 超級方程式酒吧 （輕鬆編輯多行文字和公式）; 閱讀佈局 （輕鬆讀取和編輯大量單元格）; 粘貼到過濾範圍...
- 合併單元格/行/列 不丟失數據; 分裂細胞含量; 組合重複的行/列...防止重複的細胞; 比較範圍...
- 選擇複製或唯一 行; 選擇空行 （所有細胞都是空的）; 超級查找和模糊查找 在許多工作簿中; 隨機選擇......
- 精確複製 多個單元格而不更改公式參考; 自動創建參考 多張表; 插入項目符號，複選框等等......
- 提取文本，添加文本，按位置刪除， 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
- 超級過濾器 （將過濾方案保存並應用到其他工作表）; 高級排序 按月/週/日，頻率等; 特殊過濾器 用粗體，斜體......
- 結合工作簿和工作表; 根據鍵列合併表; 將數據拆分為多個表格; 批量轉換xls，xlsx和PDF...
- 超過300強大的功能。 支持Office / Excel 2007-2019和365。 支持所有語言。 在您的企業或組織中輕鬆部署。 全功能30天免費試用。
Office選項卡為Office提供選項卡式界面，使您的工作更輕鬆
- 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，Publisher，Access，Visio和Project。
- 在同一窗口的新選項卡中打開並創建多個文檔，而不是在新窗口中。
- 通過50％提高您的工作效率，每天為您減少數百次鼠標點擊！
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.
-
To post as a guest, your comment is unpublished.
· 6 months agoI need to convert example 1034890 to I034890 ( Replacing first number to letter) how to do that?
-
To post as a guest, your comment is unpublished.
· 1 years agoWhy not save some time and use =ADDRESS(1,D2,4,1) function instead?
ex. If the number is 12 and you use the above it will return L1 -
To post as a guest, your comment is unpublished.
· 1 years agonumber to alphabet
=MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",A2,1)
alphabet to Number
=SEARCH(UPPER(A1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ",1) -
To post as a guest, your comment is unpublished.
· 1 years agobagaimana menukarkan angka kepada perkataan.. contoh.. 432 akan ditukarkan kepada empat ratus tiga puluh dua..
-
To post as a guest, your comment is unpublished.
· 2 years agoThanks very much bro. You saved a lot of my time
-
To post as a guest, your comment is unpublished.
· 2 years agoIf M=1, Y=2, S=3, O=4, R=5 and E=6..
Then,
In cell A1 if i enter "M", in cell A2 i should get "1"
In cell A1 if i enter "MS", in cell A2 i should get "13"
In cell A1 if i enter "OEY", in cell A2 i should get "462"-
To post as a guest, your comment is unpublished.
· 1 years agodid you get any solution for your query
-
To post as a guest, your comment is unpublished.
· 1 years agoHi
Did you found the formula for your above query please let me know. -
To post as a guest, your comment is unpublished.
· 1 years agoi need same
-
-
To post as a guest, your comment is unpublished.
· 2 years agoInput : Any Body Can Dance, in one cell
Output : ABCD,
how to convert in excel-
To post as a guest, your comment is unpublished.
· 1 years agoStill in search of this formula in excel... I'm very close to it but not closing it to more than 1 letter.
anybody can help me in this ... !!-
To post as a guest, your comment is unpublished.
-
-
-
To post as a guest, your comment is unpublished.
· 2 years agoSush convert in number
-
To post as a guest, your comment is unpublished.
· 2 years agoHi!
Is it possible to convert this > =ROUND(AVERAGE(V4732,Z4732),-1) into this >=ROUND(AVERAGE(2280,2330),-1)??
Without inputing a number? I always input the number in the formula. I don't know if you''ll get it. But if you knew what i'm
asking? Please help me. Thank you! If its not posible. Its ok :) Just asking and just curious if it can be converting :) -
To post as a guest, your comment is unpublished.
· 2 years agoi want to know if it is possible to change a Excel Cell Data to letters
for a examplei have a set of codes represents each letter
like
1=M , 2=O, 3=D , 4=E, 5=R , 6=N , 7=L , 8=I , 9=C , 0=A
so a cell date is 10 the answer should be MAA Eg ( 1=M, A=0 ) 10=MA
so a cell date is 100 the answer should be MAA Eg ( 1=M, A=0 , A=0 ) 100=MAA
so a cell date is 500 the answer should be RAA Eg (5=R , A=0, A=0 500=RAA
so a cell data is 750 the answer should be LRA Eg (7=L , 5=R , A=0 ) 750 =LRA
so a cell data is 1750 the answer should be LRA Eg (1=M ,7=L , 5=R , A=0 ) 1750 =MLRA
encryption! Can this be Done-
To post as a guest, your comment is unpublished.
· 2 years agowhat formula we can use
-
-
To post as a guest, your comment is unpublished.
· 2 years agoi want to know if it is possible to change a Excel Cell Data to letters
for a example
i have a set of codes represents each letter
like
0=A ,1=M , 2=O, 3=D , 4=E, 5=R , 6=N , 7=L , 8=I , 9=C
so a cell data is 10 ,the answer should be MA
so a cell date is 100 ,the answer should be MAA
so a cell data is 105 , the answer should be MAR
so a cell data is 1500 the answer should be MRAA
Can this be done ?
encryption -
To post as a guest, your comment is unpublished.
· 3 years agoThat's not how you convert letters to numbers
-
To post as a guest, your comment is unpublished.
· 3 years agoThanks for the tip above. But what i want to do is that i want to convert alphabet to a number of my choice not "indirect" format as illustrated. E.g. A should be understood in its cell as 5 so that i can multiply A(now 5) with another number in another cell. I will appreciate your kind assistance in this regards. Thanks.
-
To post as a guest, your comment is unpublished.
· 3 years agoYou can use his formula but just replace the letters in order in which you want them to appear. The First letter you put will be what is entered when you enter the number 1 in the cell. For example:
If you put in =MID("JGHTS",A2,1)
Where A2 is the cell in which you want to convert. Lets say for instance cell A2 contains the number 1, in the target cell, J should pop up.
1=J
2=G
3=H
4=T
5=S -
To post as a guest, your comment is unpublished.
· 3 years agoAbuh Fred's comment above is exactly my interest as well. Any solutions? " The letter "A" should be understood in its cell as 5 so that i can multiply A(now 5) with another number in another cell. I will appreciate your kind assistance in this regards. Thanks."
Specifically I want to enter R/Y/G into a cell and be able to use the conditional formatting icon set to return just a red/yellow/green circle.
-
English
العربية
简体中文
繁體中文
Nederlands
Français
Deutsch
Italiano
日本語
한국어
Português
Русский
Español