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.

 

[GTO]

hiteshkataria,

Please try sanrv1f's (Sankar's), mine fouls if there is more than one value in the cell with a 'numeric extension'; his does not.

Hi Sankar,

same is the case here,...

...Mark,

Thanks for the invite,

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

Well, you seem to be 'crawling' quite a bit quicker!

You are most welcome of course; due to our time difference, I have not been able to participate as much as I'd like. I certainly appreciate the continued interaction.

Nice point as to A-Za-z; I also caught that you didn't worry about more than one digit at the start, as it would still match. Will study this a bit more later today/this evening.

Mark

 

[pgc01]

Hi

I'm just leaving and couldn't test properly, but try:

Function Proper1(s As String) As String
    Proper1 = StrConv(s, vbProperCase)
End Function

 

[hiteshkataria]

Mark & Shankar,

Thanks a lot! Both the code works fine.
Can you please explain me the code. I am not aware of this functionality.

Thanks.

 

[hiteshkataria]

PGC,
Your code works, as well. And its just 2 lines....WOW!

 

[jbeaucaire]

PGC,
Your code works, as well. And its just 2 lines....WOW!

Looks like 1 line to me...hehe.

That just went into the "code bank"....great stuff gang.

 

[West Man]

Some address problem may still persist. Some abbreviations should remain upper case such as AFB, US when part of US Highway 30 and POB. Others such as McWilliams Street will also be difficult to handle.

 

[pgc01]

Some address problem may still persist. Some abbreviations should remain upper case such as AFB, US when part of US Highway 30 and POB. Others such as McWilliams Street will also be difficult to handle.

Hi West Man

A remark: Your observation refers to the specification, not to the solutions.

hiteshkataria specified his problem in a clear way:

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

Notice that I think that what you are referring to is important in some cases, but if it's needed it must be specified in a rigorous way. Ex.: instead of "such as US and POB", it must be something like "except for the words: US, POB, NY and CA. These words should be all uppercase", so that there are no doubts.

 

[West Man]

Hi PCG,

You are absolutely correct in that I anticipated a possible problem that was specificly excluded by the OP. I got caught up thinking about my solution to those cases where the Proper function did not give me the results I desired. I agree that rigorous definitions are required. I learned the hard way when I looked at a word as defined by a series of characters followed by a space. Knowing that Se followed by a space should be SE. This worked fine for 1234 SE Maple St but failed for 1234 Maple St SE.

 

[pgc01]

Hi PCG,

You are absolutely correct in that I anticipated a possible problem that was specificly excluded by the OP. I got caught up thinking about my solution to those cases where the Proper function did not give me the results I desired. I agree that rigorous definitions are required. I learned the hard way when I looked at a word as defined by a series of characters followed by a space. Knowing that Se followed by a space should be SE. This worked fine for 1234 SE Maple St but failed for 1234 Maple St SE.

I think the most important part in this "real world" problems is the part of the rigorous specification. It requires first extensive analysis and then strong synthetic thinking.

After you have your specification, if you think we can help, open a new thread and post here a link to it. As you've seen in Sankar's and Mark's solutions, the use of regular expressions may help a lot in this type of problems.

 

[hiteshkataria]

I tried to use the below code again, but it shows an error in cells as #NAME?

Function Proper1(s As String) As String
    Proper1 = StrConv(s, vbProperCase)
End Function