Hi Tom,

Thanks for your second reply today!

Yes, I see what you mean about the dots, however as I read it, however, I
only want to indent the items in column B. The other values in the "dotted"
string are not required.

Anyway, thanks again and I will certainly use the Replace function in
future.

Best regards

John

"Tom Ogilvy" wrote in message
...
Or you could skip counting the dots and let them represent themselves.

Option Explicit

Sub IndentListWithSpaces()

'Run down list and indent cell values to right,
'dependent on number of dots in string

Dim rgListItem As Range
Dim r As Integer
Dim c As Integer
Dim Answer As Long

'Check user selects cell at top of list
Answer = MsgBox(Prompt:="Is cell at top of list selected?", _
Buttons:=vbYesNo + vbQuestion)
If Answer = vbNo Then Exit Sub

r = ActiveCell.Row
c = ActiveCell.Column

Do
Set rgListItem = Cells(r, c)
If IsEmpty(Cells(r, c)) Then Exit Do
With rgListItem.Offset(0, 1)
.Value = Replace(rgListItem.Text,"."," ") & .Value
' or for xl97
' .Value = Application _
' .Substitute(rgListItem.Text,"."," ") & .Value
End With
r = r + 1
Loop

Cells(r, c).Select

MsgBox "Finished"

End Sub

--
Regards,
Tom Ogilvy