I have long lists of information that have been downloaded from a serve
and it needs to be formatted in a specific way. The information i
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 righ
way to write the macro to make this work.

The Cell Format dosen't change the information either.

Any help is appreciated.

Thank

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

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/

Shively5,
The macro below works for me. copy it to a module.
If you are using "0" as the first charcter as your first example you
you should set you Columns to "TEXT".

HTH

Chales

Sub add_space()
Application.ScreenUpdating = False
Dim fchr, fchr1, fchr2, fchr3, fchr4, fchr5, dchr
Dim cellrange As Range
Dim lenofcell
Dim F As Long
Dim i As Long
lenofcell = Len(ActiveCell) 'SETTING LENGTH OF CELL
Set cellrange = Worksheets("sheet1").UsedRange
F = 1
For i = 1 To cellrange.Rows.Count ''''loops thru column A
fchr = Mid(cellrange(i, 1).Value, F, 2)
fchr1 = Mid(cellrange(i, 1).Value, F + 2, 4)
fchr2 = Mid(cellrange(i, 1).Value, F + 6, 2)
fchr3 = Mid(cellrange(i, 1).Value, F + 8, 3)
fchr4 = Mid(cellrange(i, 1).Value, F + 11, 2)
fchr5 = Mid(cellrange(i, 1).Value, F + 12, 2)
dchr = fchr & " " & fchr1 & " " & fchr2 _
& " " & fchr3 & " " & fchr4 & " " & fchr5
cellrange(i, 1).Offset(0, 1).Value = dchr '''' adds results t
column B
Next
End Su

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

It isn't obvious with the test data provided, but if you use

1234567890abcde

you produce
12 3456 78 90a bc cd

So you have an error in your code.

You should change to

fchr5 = Mid(cellRange(i, 1).Value, F + 13, 2)

--
Regards,
Tom Ogilvy

Charles wrote in message
...
Shively5,
The macro below works for me. copy it to a module.
If you are using "0" as the first charcter as your first example you
you should set you Columns to "TEXT".

HTH

Chales

Sub add_space()
Application.ScreenUpdating = False
Dim fchr, fchr1, fchr2, fchr3, fchr4, fchr5, dchr
Dim cellrange As Range
Dim lenofcell
Dim F As Long
Dim i As Long
lenofcell = Len(ActiveCell) 'SETTING LENGTH OF CELL
Set cellrange = Worksheets("sheet1").UsedRange
F = 1
For i = 1 To cellrange.Rows.Count ''''loops thru column A
fchr = Mid(cellrange(i, 1).Value, F, 2)
fchr1 = Mid(cellrange(i, 1).Value, F + 2, 4)
fchr2 = Mid(cellrange(i, 1).Value, F + 6, 2)
fchr3 = Mid(cellrange(i, 1).Value, F + 8, 3)
fchr4 = Mid(cellrange(i, 1).Value, F + 11, 2)
fchr5 = Mid(cellrange(i, 1).Value, F + 12, 2)
dchr = fchr & " " & fchr1 & " " & fchr2 _
& " " & fchr3 & " " & fchr4 & " " & fchr5
cellrange(i, 1).Offset(0, 1).Value = dchr '''' adds results to
column B
Next
End Sub


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

Tom,

Thanks for the correction, I too am still learning.

Charle

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