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

or

如何在Microsoft Excel中將大寫更改為小寫?

假設您收到一個包含大寫字母的所有文本字符串的Excel報表,如果您可以在Excel中將這些大寫字母更改為小寫,則會更容易閱讀,如下面的屏幕截圖所示。 以下技巧可以在Excel中快速將所有大寫字母更改為小寫。


將文本字符串更改為小寫,大寫,正確的大小寫...

Kutools for Excel's 改變案例 實用程序可以幫助您根據需要將文本字符串更改為大寫,小寫,正確大小寫,句子大小寫和切換大小寫。 點擊下載Kutools for Excel!

doc更改為小寫12


方法1:Excel用於將文本更改為小寫

Microsoft Excel支持Lower函數將任何類型的文本字符串更改為小寫,請執行以下操作:

1。 在相鄰的空白單元格D1中,輸入或複制公式

=下(A2)

並按下 輸入 得到第一個結果的關鍵。 看截圖:

doc更改為小寫1

2。 然後,選擇單元格D1並將填充柄向下拖動到要應用此公式的單元格,並將所有文本字符串轉換為小寫字母,請參見屏幕截圖:

doc更改為小寫2

3。 然後,您應該複製轉換後的單元格並將其作為值格式粘貼到原始單元格中。

doc更改為小寫3

筆記:

要將文本字符串轉換為大寫,請應用以下公式:

=UPPER(A2)

doc更改為小寫4

要將文本字符串轉換為正確的大小寫,這意味著要將每個單詞的首字母大寫,請應用以下公式:

=PROPER(A2)

doc更改為小寫5


辦公室標籤圖片

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

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

方法2:Flash Fill功能將文本更改為小寫

在Excel 2013及更高版本中,有一個方便的實用程序 - Flash填充使用此功能,您也可以快速解決此任務。

1。 在要更改大小寫的數據旁邊插入一個空白列,然後輸入要使用的第一個文本字符串。 例如,我將在單元格B2中鍵入帶小寫的文本字符串,請參見屏幕截圖:

doc更改為小寫6

2。 鍵入正確的格式化文本字符串然後選擇它,然後單擊 主頁 > > 填寫Flash,此列中的其餘單元格將填充小寫文本字符串,請參見屏幕截圖:

doc更改為小寫7

筆記:

1。 要獲取所有大寫文本,請在第一個單元格中鍵入大寫文本,然後應用 Flash填充 功能;

2。 要獲取所有正確的案例文本,請在第一個單元格中鍵入首字母大寫的文本字符串,然後應用 Flash填充 功能;


方法3:將文本更改為小寫的VBA代碼

以下VBA代碼還可以幫助您根據需要更改文本字符串大小寫,轉換後的結果將直接替換為原始值。

1。 按住 Alt + F11鍵 鍵打開 Microsoft Visual Basic for Applications 窗口。

2。 點擊 插入 > 模塊,並將以下代碼粘貼到模塊窗口中。

VBA代碼:將文本字符串更改為小寫:

Sub LCase()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    Rng.Value = VBA.LCase(Rng.Value)
Next
End Sub

3。 然後,按 F5 鍵運行此代碼,在彈出的提示框中,選擇要轉換的單元格範圍,請參見截圖:

doc更改為小寫8

4。 然後,點擊 OK 按鈕,所選單元格中的所有文本字符串都已轉換為小寫,請參見屏幕截圖:

doc更改為小寫9

筆記:

將文本字符串更改為大寫,請將此VBA代碼複製並粘貼到模塊中:

Sub UCase()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    Rng.Value = VBA.UCase(Rng.Value)
Next
End Sub

將每個單詞的首字母大寫,請將此VBA代碼複製並粘貼到模塊中:

Sub ProperCase()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    Rng.Value = Application.WorksheetFunction.Proper(Rng.Value)
Next
End Sub

方法4:用於Excel的Kutools將文本更改為小寫

Kutools for Excel

300高級工具,解決1500工作場景

解決80%Excel難題,提高80%的生產力

幫助您獲得促銷,
增加薪水!

由110,000精英,300 +知名公司選擇

如果你有 Kutools for Excel 安裝好了 改變案例 工具可以幫助您直接將所有文本字符串更改為小寫,大寫,正確大小寫,句子大小寫和切換單元格。

安裝後 Kutools for Excel,請這樣做:

1。 選擇要更改大小寫的單元格,然後單擊“確定” Kutools > 文本 > 改變案例,看截圖:

doc更改為小寫10 1

2。 在 改變案例 對話框中選擇 小寫 選項,然後單擊 OK or 應用 按鈕,所有文本字符串都已轉換為您想要的小寫,請參見屏幕截圖:

doc更改為小寫11

提示: 這個 改變案例 實用程序還可以幫助您根據需要將文本字符串更改為大寫,正確的大小寫,句子大小寫和切換大小寫。


週末忙碌的工作,使用 Kutools for Excel,
給你一個輕鬆愉快的周末!

週末,孩子們大聲疾呼要出去玩,但周圍有太多工作無法陪伴家人。 太陽,沙灘和大海那麼遠? Kutools for Excel 幫助您 解決80%Excel難題,節省80%的工作時間。

pic海灘
  • 獲得晉升並增加工資並不遙遠;
  • 包含300高級功能,解決1500應用場景,一些功能甚至可以節省99%的工作時間;
  • 成為3分鐘的Excel專家,並獲得同事或朋友的認可;
  • 不再需要從谷歌搜索解決方案,告別痛苦的公式和VBA代碼;
  • 只需點擊幾下即可完成所有重複操作,釋放疲憊的雙手;
  • 只有$ 39,但值得其他人的$ 4000 Excel教程;
  • 被110,000精英和300 +知名公司選中;
  • 30天免費試用,並在60天內全額退款,沒有任何理由;
  • 改變你的工作方式,然後改變你的生活方式!
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.
    toolnx · 2 years ago
    Simple online tool for convert case
    Convert Case
  • To post as a guest, your comment is unpublished.
    Prabhukalyan Dhal · 2 years ago
    Is there any simple method without using any formula within the same excel sheet ?
  • To post as a guest, your comment is unpublished.
    Raghavendra · 3 years ago
    My friends Upper case and Lower case formula is =UPPER(COLOM &ROW ) ENTER
  • To post as a guest, your comment is unpublished.
    sandeep verma · 3 years ago
    how to ms excel in current cell & all selected cell upper case & lower case formula use in this work sheet . because used short key of sued key board .

    than show result.
  • To post as a guest, your comment is unpublished.
    sandeep verma · 3 years ago
    advance excel note book .

    warms regard.

    sandeep verma

    Thank's
  • To post as a guest, your comment is unpublished.
    fatima · 3 years ago
    Thank you so much i learnt what i wanted to know in excel
  • To post as a guest, your comment is unpublished.
    Jessica Vu · 4 years ago
    Thanks very much. It is so useful & easy to understanding. :-)
  • To post as a guest, your comment is unpublished.
    sharad · 4 years ago
    thanks,
    your suggestion very helpful
  • To post as a guest, your comment is unpublished.
    Sasikumar · 4 years ago
    Dear sir,

    when i strat to enter in the excel column i expected only uppercase using some formula. Please answer me
  • To post as a guest, your comment is unpublished.
    Sagar Jadhav · 4 years ago
    Thank you Very much it's very easy use ....... :-)
  • To post as a guest, your comment is unpublished.
    Ola · 4 years ago
    Can anybody help please? I am trying to perform vlookup and is coming as #N/A, i can confirm that what i am looking for actually exist in the column
  • To post as a guest, your comment is unpublished.
    Rsilva · 4 years ago
    very useful and easy

    thank you
  • To post as a guest, your comment is unpublished.
    ATULCHANDRA · 4 years ago
    VERY USEFUL ANSWER WITH MULTIPLE OPTION.
  • To post as a guest, your comment is unpublished.
    PHILEMON · 4 years ago
    THAT FOR MAKING ME UNDERSTAND
  • To post as a guest, your comment is unpublished.
    KIRAN · 4 years ago
    IT WAS USEFULTHANK YOU :) [/b]
  • To post as a guest, your comment is unpublished.
    parvathi arungopal · 4 years ago
    thanks for your good answer
  • To post as a guest, your comment is unpublished.
    mckillio · 4 years ago
    This didn't do anything, my cell (F2) just says =LOWER(E2). Does this have to be columns A and B?
  • To post as a guest, your comment is unpublished.
    Diljith · 4 years ago
    This looks heavily complex process. I am saying this for those who love shortcuts and less brain burns. thing is simple, copy the cell which you want to change the case, paste in a new blank word document, change its case and copy and paste it back into excel,BINGO !!!!...you're done
  • To post as a guest, your comment is unpublished.
    Nandu · 5 years ago
    Is there a short key for this ??
  • To post as a guest, your comment is unpublished.
    naim · 5 years ago
    how to set uppercase text with number.
    for example : jun-14
    • To post as a guest, your comment is unpublished.
      Rohit Karle · 5 years ago
      U just need to have a single quote(') for the numbers which are date.
      Eg. 'jun-14
      Use Formula =UPPER(CELL NUMBER).
      If it is other than date and all, You don't need to have single quote.
  • To post as a guest, your comment is unpublished.
    Jeanne B. · 5 years ago
    There is an easier way. In Excel, copy the entire column. Launch Word. Make new document. Paste. Use Word's case change button to change case. Select all. Copy. Return to Excel. Paste.


    Or... MAYBE EXCEL COULD INSTALL A CASE CHANGE BUTTON?
  • To post as a guest, your comment is unpublished.
    Basha · 5 years ago
    Very Helpful.

    Thank You
  • To post as a guest, your comment is unpublished.
    Madhuri · 5 years ago
    how to small letters change to capital letter in key board excel 2007
  • To post as a guest, your comment is unpublished.
    John Smith · 5 years ago
    Thanks a lot. you have done a great job. :-) :-) :-) this is an great article
  • To post as a guest, your comment is unpublished.
    Nas · 5 years ago
    What is the next step after the below comment?
    Note: This method can only change all the uppercase letters into lowercase in a new range, so you may have to take a further step to replace the uppercase letters with the lowercase letters.


    --------------------------------------------------------------------------------
    • To post as a guest, your comment is unpublished.
      tester · 5 years ago
      [quote name="Nas"]What is the next step after the below comment?
      Note: This method can only change all the uppercase letters into lowercase in a new range, so you may have to take a further step to replace the uppercase letters with the lowercase letters.


      --------------------------------------------------------------------------------[/quote]
      copy ---> paste special ----> values
  • To post as a guest, your comment is unpublished.
    asad · 5 years ago
    Thanks. Great article. MS should use this on their documentation instead
  • To post as a guest, your comment is unpublished.
    Lisa B · 5 years ago
    Or, use function =Proper(cell) to have capitals for first letter of names, cities, etc...
    • To post as a guest, your comment is unpublished.
      Bryan · 5 years ago
      This is an even Faster way to do it using VBA instead of excel functions.

      Sub LOWER_CASE()
      Addr = Intersect(ActiveSheet.UsedRange, Selection).Address
      Range(Addr) = Evaluate("IF(LEN(" & Addr & "),LOWER(" & Addr & "),"""")")
      End Sub

      Go to developer tab>macros>(type a name for the new macro)>create. and paste above code, now every time you run the macro it will change the case of the current selection to lowercase.

      Note. if you want to use it for UPPER or Proper case, you only have to replace all the "LOWER" ' s in the code with PROPER or UPPER depending of what you need.
      • To post as a guest, your comment is unpublished.
        anna · 3 years ago
        sometimes using VBA is more confusing than excel function, especially for Excel amateur like me lol