Dim sStr as String, sStr1 as String
sStr = Cell.Text
sStr1 = Left(sStr,2) & " " & Mid(sStr,3,4) & " " & _
Mid(sStr,7,2) & " " & Mid(sStr,9,3) & " " & Mid(sStr,12,2) _
& " " & Right(sStr,2)
Cell.Value = sStr1


Verify in the immediate window:

sStr = "040005SW0010000"
sStr1 = Left(sStr,2) & " " & Mid(sStr,3,4) & " " & _
Mid(sStr,7,2) & " " & Mid(sStr,9,3) & " " & Mid(sStr,12,2) _
& " " & Right(sStr,2)
? sStr1
04 0005 SW 001 00 00

--
Regards,
Tom Ogilvy

shively5 wrote in message
...
I have long lists of information that have been downloaded from a server
and it needs to be formatted in a specific way. The information in
each cell is consistant in size but requires spaces at specific spots.
examples:

040001000000000 before change
04 0001 00 000 00 00 after change


040005SW0010000 before change
04 0005 SW 001 00 00 after change

I know this can be done, but I don't seem to be able to find the right
way to write the macro to make this work.

The Cell Format dosen't change the information either.

Any help is appreciated.

Thanks


---
Message posted from http://www.ExcelForum.com/