'Properise' text strings using VBA

  • Thread starter Thread starter Rier
  • Start date Start date
R

Rier

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 (fo
example).
So an example might be that Range("A1") contains the name 'mr john ala
smith'. Using VBA , I would want this string replaced with 'Mr Joh
Alan Smith'.

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

In the following example, let's assume the following is the text I nee
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 wa
through a string doing the necessary on route like removing fullstops
capitalising where expected, etc?

Anyone's help would be much appreciated

Thanks

Rie
 
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.
 
Rier
try this - it works on selected text.

Sub Txt()
Dim c
For Each c In Selection
c.Value = Application.WorksheetFunction.Proper(c)
Next c
End Sub

Peter Atherton
 
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
 
Back
Top