'Properise' text strings using VBA
 

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

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

'Properise' text strings using VBA
 

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/

'Properise' text strings using VBA
 

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
-----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/

.

'Properise' text strings using VBA
 

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/

.