Rier

Sorry, I was tiered when I tried answering your question.
Here is a working procedure.

Sub CleanText()
Dim c
Dim l As Integer
For Each c In Selection
c.Select
l = Len(c)
c.Value = Trim(c)
If InStr(l, c, Chr(44), 1) Or InStr(l, c, Chr(46), 1)
Then
c.Value = Trim(Left(c, Len(c) - 1))
c.Value = Application.WorksheetFunction.Proper(c)
Else
c.Value = Application.WorksheetFunction.Proper(c)
End If
Next c

End Sub

I saw Tom's reply and it looks as though the the line
c.Value = Application.WorksheetFunction.Proper(c)
can be simplified.

Regards
Peter Atherton
-----Original Message-----

Hi all,

Note: in VBA and not using Excel's built in functions

Quite simply (she says), I want to capitalise a person's
name (for
example).
So an example might be that Range("A1") contains the
name 'mr john alan
smith'. Using VBA , I would want this string replaced
with 'Mr John
Alan Smith'.

My program basically inputs a text file, seeks out the
lines that
contain contact details and I want to tidy up the lines
prior to
populating a spreadsheet.

In the following example, let's assume the following is
the text I need
to format/ tidy up.

1) mr John aLAN smith,
2) 10b the Crescent
3) norwich.
4) England

Line 1 would become Mr John Alan Smith
Line 2 would become 10b The Crescent
Line 3 would become Norwich
Line 4 would become England

Has anyone already written a little routine that will
work its way
through a string doing the necessary on route like
removing fullstops,
capitalising where expected, etc?

Anyone's help would be much appreciated

Thanks

Rier


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

.