所以我都會另外把字串加工,例如每隔5個字元就空幾格。
以往的作法都是用 RIGHT 和 LEFT 還有 LEN函數來處理,切出許多段以後,再用合併字串的方式將這些字串和空格結合。
一直到前幾天,我才發現原來VBA寫的Function,居然可以在儲存格裡以公式的形式呼叫使用,我一直以為只能在VBA內的Sub 呼叫使用。
未來我只要這樣就可以用了,給ansTransfer這個公式三個參數,分別是字串、每隔多少字元、用什麼字元分隔。以下圖為例,就是把A1的字串,每隔五個切一段,用一些空白作間隔,它就自然會做好了喔。
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEin_2_Znk4e9CcAfh1QCICQihuo-eE_RuXfVBMYvIEjHn3ajGd5RcMJ0HecGU56z2Tll2oGxvj7IQzdeuGxtSBxpBV5YI3ckp3C0dW5gQHp9BZaJUQw6MG3d7mgF5bWXUVe0Ot1/s400/excel1.png)
而這個公式在VBA裡是這樣寫的,只要把它貼到VBA的模組裡就可以使用了
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhRsz_RLK4VKsvpeIvsnjEADBPK5Q32aMEoOb41JQAHPE0OEccQ03Ov1fitluEttO8TdF1foKqRrW-IcKxwjkwNguXmLLaoBUek4uzOMax2fXSJyAKc-Ch3O0q8BRtHyvuu7XYA/s400/excel2.png)
Function ansTransfer(answer As String, space_num As Integer, space_charater As String)
'anser 輸入的答案
'space_num 每隔多少切一段
'space_charater 切段中間的字元
'計算共幾段
ans_length = Len(answer)
segments_num = ans_length \ space_num
txt = ""
For i = 0 To segments_num
txt = txt & Mid(answer, 1 + space_num * i, space_num) & space_charater
Next i
ansTransfer = txt
End Function
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEin_2_Znk4e9CcAfh1QCICQihuo-eE_RuXfVBMYvIEjHn3ajGd5RcMJ0HecGU56z2Tll2oGxvj7IQzdeuGxtSBxpBV5YI3ckp3C0dW5gQHp9BZaJUQw6MG3d7mgF5bWXUVe0Ot1/s400/excel1.png)
而這個公式在VBA裡是這樣寫的,只要把它貼到VBA的模組裡就可以使用了
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhRsz_RLK4VKsvpeIvsnjEADBPK5Q32aMEoOb41JQAHPE0OEccQ03Ov1fitluEttO8TdF1foKqRrW-IcKxwjkwNguXmLLaoBUek4uzOMax2fXSJyAKc-Ch3O0q8BRtHyvuu7XYA/s400/excel2.png)
Function ansTransfer(answer As String, space_num As Integer, space_charater As String)
'anser 輸入的答案
'space_num 每隔多少切一段
'space_charater 切段中間的字元
'計算共幾段
ans_length = Len(answer)
segments_num = ans_length \ space_num
txt = ""
For i = 0 To segments_num
txt = txt & Mid(answer, 1 + space_num * i, space_num) & space_charater
Next i
ansTransfer = txt
End Function