Question

怎样在excel单元格字符串中特定的位置插入空格

在excel中，A列，每个单元格有不规则长短的英文单词，每个单词和单词之前没有空格，但是每个单词的首字母都是大写。比如，A1为HowToDealWithIt， A2为 WhereAreYou。句子长短没有规律，单词的个数也是随机的。 怎样在B1中得到 How To Deal With It,B2 中得到Where Are You。或者是在B1中得到How，C1中得到To，D1中得到Deal，E1 中分得到It。

Answer 1

用宏来弄吧，新键一个宏，输入以下代码来执行即可。

Sub Macro2()
'
' Macro2 Macro
' 宏由 微软用户 录制，时间: 2010-2-24
'

'
Cells.Replace What:="A", Replacement:=" A", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="B", Replacement:=" B", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="C", Replacement:=" C", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="D", Replacement:=" D", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="E", Replacement:=" E", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="F", Replacement:=" F", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="G", Replacement:=" G", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="H", Replacement:=" H", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="I", Replacement:=" I", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="J", Replacement:=" J", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="K", Replacement:=" K", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="L", Replacement:=" L", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="M", Replacement:=" M", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="N", Replacement:=" N", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="O", Replacement:=" O", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="P", Replacement:=" P", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="Q", Replacement:=" Q", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="R", Replacement:=" R", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="S", Replacement:=" S", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="T", Replacement:=" T", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="U", Replacement:=" U", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="V", Replacement:=" V", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="W", Replacement:=" W", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="X", Replacement:=" X", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="Y", Replacement:=" Y", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="Z", Replacement:=" Z", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
End Sub

Answer 2

1. 首先利用find函数计算固定字符串的位置，然后再加上字符串的字符数；

2. 利用replace函数添加空格。

    

Answer 3

真正全自动的宏，不是手动录制的哟，代码如下：
Sub rplc() '分列A列单词
On Error Resume Next
Dim CLen As Integer 'length of whole string
Dim cAsc As Integer 'ASC of per character
Dim myStr As String 'total string original
Dim NewC As String 'per character
Dim temStr As String 'tem string
Dim i As Integer 'for loop
Dim m As Integer
Dim Rloop As Integer
Rloop = [a65536].End(xlUp).Row
Application.ScreenUpdating = False
' start loop
For m = 1 To Rloop
Cells(m, 1).Activate
myStr = ActiveCell.Value
CLen = Len(myStr)
For i = 2 To CLen * 2
NewC = Mid(myStr, i, 1)
cAsc = Asc(NewC)
If cAsc >= 65 And cAsc <= 90 Then
temStr = Left(myStr, i - 1) & " " & Right(myStr, CLen - i + 1)
myStr = temStr
CLen = Len(myStr)
i = i + 1
Debug.Print myStr
End If
Next i
ActiveCell.Offset(0, 1).Value = myStr
Next m
Application.ScreenUpdating = True
End Sub