Upper/Lower Case in text

[hiteshkataria]

Hi,

I have data in cell A1 as below:
36, TREE ROAD, 5TH FLOOR

I want it in proper format in cell A2 as below:
36, Tree Road, 5th Floor

I used formula =Proper(A1), it gave results as:
36, Tree Road, 5Th Floor

The only problem I face is after any number, the next character should in lowercase, i.e. 5th

Your help is highly appreciated.

Thanks.

 

[hiteshkataria]

Can anybody please respond to this thread.
Thanks.

 

[jbeaucaire]

PROPER() does exactly as you've shown. If you use FIFTH instead if 5TH you won't have an issue.

 

[hiteshkataria]

Hi jbeaucaire,

Thanks for the note!
I have all my data as numerals. Is there any other way by which I can get 5th instead of 5Th.

Thanks.

 

[GTO]

Greetings,

I am definitely 'crawling' (not even baby steps yet) in RegExp, but maybe try a UDF:

In a Standard Module:<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> PROPNUMEXT(CellAddress<SPAN style="color:#00007F">As</SPAN> Range)<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN><br>Dim _<br>REX        <SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Object</SPAN>, _<br>oMatches    <SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Object</SPAN>, _<br>strRep      <SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> REX = CreateObject("VBScript.RegExp")<br>    <br>    <SPAN style="color:#00007F">With</SPAN> REX<br>        .Global =<SPAN style="color:#00007F">True</SPAN><br>        .IgnoreCase =<SPAN style="color:#00007F">True</SPAN><br>        .MultiLine =<SPAN style="color:#00007F">False</SPAN><br>        .Pattern = "[\d]+[A-Za-z]{2}\b"<br>        <SPAN style="color:#00007F">If</SPAN> .Test(CellAddress.Value)<SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> oMatches = .Execute(CellAddress.Value)<br>            strRep = oMatches(0)<br>            PROPNUMEXT = .Replace(CellAddress.Value, LCase(strRep))<br>        <SPAN style="color:#00007F">Else</SPAN><br>            PROPNUMEXT = "ERRRET"<br>        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Function</SPAN></FONT>

Entered like:

Excel Workbook
	A	B
1	36, TREE ROAD, 5TH FLOOR	36, TREE ROAD, 5th FLOOR
2	42ND St.	42nd St.
3	123 W. 23RD Ave.	123 W. 23rd Ave.
4	521 South 42ND Way	521 South 42nd Way
5	1001A West 32nd Pl	1001A West 32nd Pl
Sheet1

Excel 2000

Cell Formulas
Range	Formula
B1	=PROPNUMEXT(A1)
B2	=PROPNUMEXT(A2)
B3	=PROPNUMEXT(A3)
B4	=PROPNUMEXT(A4)
B5	=PROPNUMEXT(A5)

Hope that helps,

Mark

 

[hiteshkataria]

Wow! That's great!

I need to first use Proper() function and then Propnumext(). Can't I do both in a single step?

 

[GTO]

Why do you first need to use PROPER? I tested on the data as shown. If the 'input' data is different, could you show some more example data?

Mark

 

[hiteshkataria]

As posted in my first post, the input data is
36, TREE ROAD, 5TH FLOOR

Thanks.

 

[sanrv1f]

I am definitely 'crawling' (not even baby steps yet) in RegExp, but maybe try a UDF:

same is the case here, here is my try

Function myproper(ByVal ref As String) As String
Dim ar, i As Integer
With CreateObject("VBScript.Regexp")
    .Pattern = "[0-9][A-Za-z]{2}\b"
    .Global = True
    If .test(ref) Then Set ar = .Execute(ref)
End With
myproper = Application.Proper(ref)
For i = 0 To ar.Count - 1
    myproper = Replace(myproper, ar(i), LCase(ar(i)), compare:=vbTextCompare)
Next
End Function

Excel Workbook
	A	B
3	36, TREE ROAD, 5TH FLOOR	36, Tree Road, 5th Floor
4	42ND St.	42nd St.
5	123 W. 23RD Ave.	123 W. 23rd Ave.
6	521 South 42ND Way	521 South 42nd Way
7	1001A West 32nd Pl	1001A West 32nd Pl
Sheet3

Excel 2003

Cell Formulas
Range	Formula
B3	=myproper(A3)

Mark,

Thanks for the invite,

since we use '[A-Za-z]' in our pattern, I dont think we need 'Ignorecase' option

 

[GTO]

Oops! I did not notice the street name being in UCASE, try:
<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> PROPNUMEXT(CellAddress <SPAN style="color:#00007F">As</SPAN> Range) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>Dim _<br>REX         <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, _<br>oMatches    <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, _<br>strRep      <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, _<br>strIn       <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> REX = CreateObject("VBScript.RegExp")<br>    <br>    strIn = StrConv(CellAddress.Value, vbProperCase)<br>    <br>    <SPAN style="color:#00007F">With</SPAN> REX<br>        .Global = <SPAN style="color:#00007F">True</SPAN><br>        .IgnoreCase = <SPAN style="color:#00007F">True</SPAN><br>        .MultiLine = <SPAN style="color:#00007F">False</SPAN><br>        .Pattern = "[\d]+[A-Za-z]{2}\b"<br>        <SPAN style="color:#00007F">If</SPAN> .Test(strIn) <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> oMatches = .Execute(strIn)<br>            strRep = oMatches(0)<br>            PROPNUMEXT = .Replace(strIn, LCase(strRep))<br>        <SPAN style="color:#00007F">Else</SPAN><br>            PROPNUMEXT = "ERRRET"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

Does that work?

Mark