如何在Excel中插入大寫字母之前的空格？

自我感覺良好

如何在Excel中插入大寫字母之前的空格？
為了要資料剖析…
否則太傷眼力了…

(n) 館長；管理者 The director gave us work to do.
(n) 履歷，簡歷 If you want a job, write a good resume.
(n) 知識；學問 She has knowledge of ancient history.
(v) 討論 Let's discuss your idea.
(n) 地理；地理學 Steve likes to learn about geography.
(n) 電影 This movie is exciting!
(n) 疊；堆 Put all those books in a pile.
(n) 經驗；經歷 Jen has many years of experience.
(phr v) 瀏覽，快速看 Tony looked through all the books.
(adj) 生來就有的；出生的 Wendy was born in August.
(v) 懷疑；不相信 I doubt Liam knows all the answers.
(n) 票；入場券 We need tickets for the show.
(n) 爆米花 Let's buy some popcorn.
(n) 飲料 Fran buys drinks at the store.
(n) 場景，一幕戲；鏡頭 I enjoy the action scenes.
(dj) 有趣的；滑稽的 Glenn told us  funny story.
(v) 穿（衣服）；戴（首飾等） Wht ht will you wer?
(n) 大衣；外套 The coat is too big for you.
(n) 可口可樂 Jesse wants a Coke.
(n) 種類 There are many kinds of dogs.
(n) 冒險（活動、經歷） Let's go on an adventure this weekend!
(adj) 可怕的；嚇人的 Stella saw a scary movie last night.
(adj) 溫暖的；暖和的 This jacket keeps me warm.
(n) 靴子 My favorite boots are brown.
(adj) 積雪的；下雪的；多雪的 He walked through the snowy field.
(adj) 緊的 These pants are too tight.
(n) 一雙，一副 Stella is wearing a new pair of pants.
(n) 演出節目；表演 Let's go see a show at the theater.
(n) 舞臺 He watched the play on the stage.
(v) 舉起，提起，升起 She couldn't lift the heavy suitcase.
(n) 毛衣，針織套衫 This sweater is a gift from my mom.
(n) 圍巾 He wore a red scarf around his neck.
(n) 連指手套 These mittens are too big for my hands.
(n) 舞蹈演員；跳舞者 There are many good dancers in this show.      dance(v) 跳舞      Let's dance to the music.
(n) 觀眾，聽眾，與會者 The audience laughed at the funny show.
(adv) 幾乎，差不多，將近 Emma is almost 10 years old.
(v) 數（按順序）；計算 Can you help me count the books?
(prep) 加（上） Two plus eight is ten.
(v) 除以 Divide 10 by two and you get five.
(n) 元（美國、加拿大等國的貨幣單位） The toy costs 50 dollars.
(v) 減去 Subtract six from 12 to get six.
(adj) 近的，靠近的 I sat close to the wall.
(v) 把…相加 Add the numbers together.
(n) 總數，合計 Add the numbers and tell me the total.
(v) 乘，使相乘 Multiply four times four to get 16.
(n)(一）美分（幣值） There are 100 cents in a dollar.
(prep) 減（去） Four minus three is one.
(adj) 遠的；遙遠的 My home is too far away.
(v) 檢查 The nurse checks your blood pressure.
(adj) 清潔的，乾淨的 Janice keeps a clean kitchen.
(n) 島嶼 Take a boat to the island.
(n) 瀑布 Lots of water comes over the waterfall.
(adj) 美味可口的 This food is really delicious!
(n) 鳳梨 Her favorite fruit is pineapple.
(n) 隱形眼鏡 Do you wear contact lenses?
(adj) 美麗的，漂亮的 Let's watch the beautiful sunset.
(n) 英里（相當1.6公里） I walked 3 miles to her house.
(n) 火山 Smoke is coming from the volcano.
(n) 魚肉；魚 There are many fish in the ocean.
(n) 風景，景色 I love the scenery near the mountains.
(n) 海洋；大海 Penny went to swim in the ocean.
(n) 熊 Look at that big bear!
(n) 狐狸 That tiny fox has big ears.
(n) 長頸鹿 Giraffes don't drink a lot of water.
(n) 獅子 How many lions are at this zoo?
(n) 犀牛 Rhinoceroses are big animals.
(n) 大象 Elephants weigh a lot.
(n) 猴子 The monkey ran up the tree.
(n) 事實；真相；實際資訊 Gary knows a lot of facts.
(n) 公尺，米 Taipei 101 is more than 500 meters tall.
(n) 老虎 The tiger moved quietly.
(n) 公斤 The dog weighed just 3 kilograms.
(n) 風格；作風 These boots aren't my style.
(n) 牛仔褲 Put on a pair of jeans.
(adj) 很糟糕的；極差的 That movie was terrible!
(v) 盡情享用（美味佳餚） We have tons of food to feast on.
(n) 番薯，地瓜 Sweet potatoes are delicious.
(adj) 感恩的；感謝的 I am thankful for good friends.
(adj) 流行的，時髦的 These clothes are very fashionable.
(n) 洋裝，連衣裙 She often wears a dress to school.
(v) 慶祝，慶賀 When do you celebrate your birthday?
(n) 火雞肉；火雞 We ate turkey all week.
(n) 蔓越莓醬 My mom makes great cranberry sauce.
(n) 頭腦；心智 Reading can calm your mind.
(v) 降低，（使）下降 Can you lower your voice?
(adj) 負面的，消極的 Dana can be very negative sometimes.
(n) 副本，複製品，拷貝 How many copies do you need?
(n) 按鈕，按鍵 Push this button on the machine.
(idiom) 幫（某人）忙 Can you give me a hand with this table?
(n) 郵局 Take these letters to the post office.
(adj) 生氣的，發怒的 My parents are angry at me for lying.
(n) 同事，同僚 My coworkers and I get along well.
(v) 選擇 Choose a color.
(n) 箱；盒；匣 Put the books in this box.
(adj) 樂意的，願意的 I'm willing to take the dog for a walk.
(n) 難題；困境 Kate is having difficulty with her car.
(n) 電腦 Use a computer to find the information.
(adj) 傷心的，令人難過的 I was sad to leave.
(pl n) 感覺；感受；情緒 Mia has mixed feelings about the move.
(v)(使）聚焦；（使）集中目光 You should focus more on your work.
(adj) 抱有希望的，充滿希望的 He's hopeful he'll win the game.
(adj) 不確定的；缺乏信心的 I am unsure when I start a new project.
(v) 忘記 Don't forget to do your homework.
(adj) 混合的，摻雜的 The play got mixed reviews.
(adj) 不高興的；不滿意的 Eric is unhappy with his job.
(adj) 高興的；愉快的 I'm so glad the weather is nice!
(v)(使）開心；（使）滿意 We're so pleased you came to visit.
(v) 加入；參加 He joined the gym this week.
(n) 網球（運動） Jim watches tennis on TV.
(n) 球場（網球，籃球等） Let's play tennis on this court.
(prep) 越過（從一邊到另一邊） The bridge goes across the river.
(n) 球拍（網球、羽球等） Martina needs a new racket.
(n) 球網（分隔雙方） Kelsie hit the ball into the net.

n7822123

回復 1# 自我感覺良好


只舉一個當做範例，若要改多個，請自行套用迴圈
(n) 館長；管理者The director gave us work to do.
變成
(n) 館長；管理者 The director gave us work to do.

Sub test()
A = "(n) 館長；管理者The director gave us work to do."
For i = 1 To Len(A)
  If Asc(Mid(A, i, 1)) >= 65 And Asc(Mid(A, i, 1)) <= 90 Then A = Left(A, i - 1) & " " & Mid(A, i): Exit For
Next
MsgBox A
End Sub

GBKEE

回復 2# n7822123

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim A As Range, B As Variant, i As Integer
   
4.     For Each A In Range("A1", Range("A1").End(xlDown))  'A1 往下到最後一筆資料
   
5.         B = ""
   
6.         For i = 1 To Len(A)
   
7.             If Mid(A, i, 1) Like "[A-Z]" Then B = IIf(B = "", Mid(A, i, 1), B & "," & Mid(A, i, 1))
   
8.             '找到大寫的字母 導入變數
   
9.         Next
   
10.         If B <> "" Then        '有找到大寫的字母
    
11.             B = Split(B, ",")  '找到大寫字母的陣列
    
12.             For i = 0 To UBound(B)
    
13.                 A = Replace(A, B(i), " " & B(i))  '加入空格
    
14.             Next
    
15.             Do While InStr(A, Space(2))         '包含有兩格的空白字元
    
16.                 A = Replace(A, Space(2), Space(1))  '消除 兩格的空白字元 為一格的空白字元
    
17.             Loop
    
18.         End If
    
19.     Next
    
20. End Sub

複製代碼

准提部林

Sub TEST_01()
Dim xR As Range, T$, C%
For Each xR In Range([A1], [A65536].End(xlUp))
    For i = 2 To Len(xR)
        T = Mid(xR, i, 1)
        If T Like "[A-Z]" And Mid(xR, i - 1, 1) <> " " Then C = 1: Exit For
    Next i
    xR(1, 2) = Replace(xR, T, " " & T, 1, C):  C = 0
Next
End Sub

自我感覺良好

太好用了…
真的是救苦救難的菩薩
太感謝了～
https://drive.google.com/file/d/1BV4Rm3Zxx_fWvka9OEGhb0eEgciRGZ9i/view?usp=sharing

n7822123

回復 5# 自我感覺良好


謝謝版主s，Like還蠻好用的，的確這樣寫簡單多了!

stillfish00

回復 1# 自我感覺良好

1. Sub Test()
   
2.     Dim reg As Object
   
3.     Set reg = CreateObject("vbscript.regexp")
   
4.     reg.Pattern = "([A-Z])"
   
5.     s1 = "(n) 館長；管理者The director gave us work to do."
   
6.     s2 = reg.Replace(s1, " $1")
   
7.     MsgBox s2
   
8. End Sub

複製代碼