v1= "mr John aLAN smith, 10b the Crescent, norwich. England"


? strconv(v1,vbProperCase)
Mr John Alan Smith, 10b The Crescent, Norwich. England

Application.Trim uses the Excel trim function

Application.Substitute uses the Excel substitue function - in xl2000 and
later, VBA has added a similar replace function

So that gets part of it.


--
Regards,
Tom Ogilvy


"Rier" wrote in 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/