Text Manipulation
Posted by Lee Hitchen on December 12, 2001 7:58 AM
Hi ll
I have a string of text in cell a1 which reads "ABCDE". How can I insert a space between each of these letters.
Have looked at text functions but nothing jumps out at me.
Regards
Lee
Posted by Shamsuddeen. P.K on December 12, 2001 8:10 AM
Try the following
Sub InsertSpace()
Text = Range("A1").Value
Length = Len(Text)
For i = 1 To Length
NewText = NewText + Mid(Text, i, 1) + " "
Next i
Range("B1").Value = NewText
End Sub
The above will copy the text from Cell A1 to Cell B1 after inserting spaces in between.
Regards,
Shamsuddeen. P.K
Posted by Joe Was on December 12, 2001 10:27 AM
Posted by Joe Was on December 12, 2001 10:33 AM
An extension of this will format a new cell the way you want given a data cell: =IF(C1<>" ",MID(C1,1,1)&" "&MID(C1,2,1)&" "&MID(C1,3,1)," ")
Just keep adding &" "&Mid(C1,position,1) for each new character in your data. I tried this with conditional formating and cell formating with the space character "_" but could not get thekm to work? JSW
Posted by Joe Was on December 12, 2001 3:08 PM
Code UpDate
This is an modification of the macro submitted by S.P.K., the modification will alow you to asign a hot key to the macro with Macros-Macro-Options, which will format the cell to the right of your data with the wanted spacing. To work it mouse click the data cell (anywhere on the sheet) and type your Ctrl+X hot key. The cell right of your selection will get a formatted copy! JSW
Sub SpaceText()
Text = Selection
Length = Len(Text)
For i = 1 To Length
NewText = NewText + Mid(Text, i, 1) + " "
Next i
Selection.Offset(0, 1) = NewText
End Sub
JSW
Posted by Lee Hitchen on December 13, 2001 7:07 AM
Re: Code UpDate
Many thanks to you both
