Excel 2007 cell format help!!!

[Raner]

Hi I'm Stuck
Need to format a Cell with spacing.


If I type this:1H2KL125UIM879HG5
How do I format a cell to come out like this:
1 H 2 K L 1 2 5 U I M 8 7 9 H G 5
It's got to be simple to do but I'm just missing it. Thanks Raner

[BjornS]

Hello Raner,
I'm stuck too! I have searched and searched ("googled around"), and it is really annoying to find out that there probably isn't any such possibility by using custom (text) format.

I suggest you use a macro function. I attach a proposal. Please note that I am a "formula guy". This is the first macro-function I am writing, but at least it seems to work fine anyhow :-)

Best regards
Bjorn

[Raner]

Hey BjornS
I'm sorry it took me so long to thank you for the came back I have several Email address's and it's its been a while since I visited the one I got your reply on.But I figured it out I made it work using another predsheet and than typed in information and than used the = & enter keys. Using a cell for every letter and number and then I spaced the cells to the right distance between the letters and numbers.The only thing I am fighting now is the cells with the number 0 will not work I custom designed the cells with 0 and it still won't work but I'm using the letter O instead of the number 0 and I am getting by for now till I can firgure what is going on.Thanks again Raner

[BjornS]

Hi Raner,
if you post an example spreadsheet, I (or someone else) can have a look at your issue with the zeros.

Best regards
Bjorn

[fzuazua]

Activate or click on the cell that has the string and run this macro it will put the spaced string in the next cell down. Assume its on Sheet1

Sub SpaceText ()
With Selection
Zrow = .Row + 1
Zcol = .Column
End With
With Worksheets("Sheet1")
.Cells(Zrow, Zcol) = ""
For I = 1 To Len(Selection.Text)
.Cells(Zrow, Zcol) = .Cells(Zrow, Zcol) & Mid(Selection.Text, I, 1) & " "
Next
End With
End sub