Adding Spaces Between Characters

[madhuchelliah]

Hello all, I want to add spaces between the characters in the string. The macro should add space between each character.If already space is exist it should ignore and add only where space is required. The string is in last used row of H column. Thank you.

 

[mole999]

how often are you doing that. a quick find replace should suffice, and that could be recorded

 

[AlphaFrog]

[color=darkblue]Sub[/color] Insert_Spaces()
    [color=darkblue]Dim[/color] str1 [color=darkblue]As[/color] [color=darkblue]String[/color], str2 [color=darkblue]As[/color] [color=darkblue]String[/color], i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]With[/color] Range("H" & Rows.Count).End(xlUp)
        str1 = .Value
        [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] Len(str1)
            str2 = str2 & Mid(str1, i, 1) & " "
        [color=darkblue]Next[/color] i
        .Value = Application.Trim(str2)
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Rick Rothstein]

Assuming normal ASCII characters (with ASCII codes less than 256), then here is another macro that you might want to consider (it does not use any loops)...

[table="width: 500"]
[tr]
	[td]Sub InsertSpacesBetweenCharacters()
  With Cells(Rows.Count, "H").End(xlUp)
    .Value = Application.Trim(Replace(StrConv(.Value, vbUnicode), Chr(0), " "))
  End With
End Sub[/td]
[/tr]
[/table]

 

[rlv01]

[TABLE="width: 500"]
<tbody>[TR]
[TD]    .Value = Application.Trim(Replace(StrConv(.Value, vbUnicode), Chr(0), " "))[/TD]
[/TR]
</tbody>[/TABLE]

That's clever