How to add a space after each character

[falcios]

How do I add space after each character and digit in Excel 2010.

For example
ABC -> A B C
123 -> 1 2 3

Thanks in advance.

 

[Rick Rothstein]

If speed became an issue, & a udf was the desired option, I would also consider also

Function SpaceIt(s As String) As String
  Dim i As Long
  Dim tmp As String

  If Len(s) Then
    tmp = Space(Len(s) * 2 - 1)
    For i = 1 To Len(s)
      Mid(tmp, i * 2 - 1) = Mid(s, i, 1)
    Next i
    SpaceIt = tmp
  End If
End Function

For me, this tested 20-25% faster than your code, though I won't be surprised if you don't agree, as the time difference between our original codes was not nearly as marked when I tested as the difference you reported.

It tested between 25% and 50% faster for me. What is annoying is I originally tried working with VB's string functions and ran into problems (getting ASCII 63 characters back) and so I gave up, but your code looks somewhat like what I think I tried starting with, but, as I said, I had nothing but problems with whatever it was I tried originally. I figured VB string functions were not Unicode friendly, but obviously I must have been doing something "wrong" originally because your code works fine for me (now).

 

[Peter_SSs]

I.. annoying ..... ran into problems... gave up,.. had nothing but problems .. not Unicode friendly,...

Have you tried thumping the box?

 

[pgc01]

... Can you post the code you had in mind? Anyway, I figured instead of battling VB over this, that using Byte arrays should work... and besides, Byte arrays handle strings quite quickly (see my response to Peter below).

Hi

I see the theme has advanced, but the code I had in mind what just looping through the characters and add a space.
I add the test for the case of characters out of the Basic Multilingual Plane (BMP), that use 2 surrogate code points.

Function AddSpace(s As String)
Dim s1 As String
Dim j As Long

For j = 1 To Len(s) - 1
    s1 = s1 & Mid(s, j, 1)
    If (AscW(Mid(s, j, 1)) And &HFC00) <> &HD800 Then s1 = s1 & " "
Next j
AddSpace = s1
End Function

 

[pgc01]

oops, sorry, I forgot to add the last character

Function AddSpace(s As String)
Dim s1 As String
Dim j As Long

For j = 1 To Len(s) - 1
    s1 = s1 & Mid(s, j, 1)
    If (AscW(Mid(s, j, 1)) And &HFC00) <> &HD800 Then s1 = s1 & " "
Next j
AddSpace = s1 & Right(s, 1)
End Function