Can you format cells as Title Case?

[George J]

I know in word that you can format a table cell so that the text is in Title Case, ie. the first letter of every word is capitilised. What i am trying to do though is capitalise the first letter of every word and have the rest in lower case, except the last line in a cell.

I am doing this for names and addresses, but the data we will be copying is all in capitals.
I need to make this look a little better for doing a mailshot, but we don't want the last line capitalised as this will be the postcode and should be all capitals. eg AB12 3CD.

Will i need to loop through every letter to change them or is there an actual formatting i can use? Also would i need to try to use some kind of lookup to find the last code(10) and make sure that this stayed as capitals?

Really not sure how to tackle this. Is there something in Word that would do the trick?

thanks

 

[Norie]

George J

If the address is on different lines but all in the same cell that indicates each line is separated by something - a linebreak.

If that's the case you probably could split it out into separate cells.

Then the formatting would probably be straightforward.

 

[Gerald Higgins]

From post #10

postcode is ... always on the last line

From post #6

all in the same cell

Let's be absolutely clear about this.
Is it in the same cell or not?
Maybe it's all in the same cell, but the postcode is on the last line in that cell.
Is that it ?
In which case, have you tried the formula I suggested in post #9 ?

I think I'm right in saying that valid UK postcodes must be at least 6 characters (including the space) not 5.

 

[Norie]

Right I started with this where each line was entered followed by ALT+ENTER.

<TABLE style="WIDTH: 89pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=119 x:str><COLGROUP><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><TBODY><TR style="HEIGHT: 51pt" height=68><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 89pt; HEIGHT: 51pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=68 width=119>GEORGE J
HIGH STREET
EDINBURGH
EH1 1AA</TD></TR><TR style="HEIGHT: 63.75pt" height=85><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 89pt; HEIGHT: 63.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=85 width=119>GERALD HIGGINS
UNION STREET
ABERDEEN
ABERDEENSHIRE
AB12 3CD</TD></TR></TBODY></TABLE>

After this code:

Option Explicit
Sub SplitAddress()
Dim rng As Range
Dim arrAddr
Dim I As Long
 
    Set rng = Range("A1")
    
    While rng.Value <> ""
        arrAddr = Split(rng.Value, Chr(10))
        For I = LBound(arrAddr) To UBound(arrAddr)
            rng.Offset(, I + 2) = arrAddr(I)
        Next I
        
        Set rng = rng.Offset(1)
        
    Wend   
    
End Sub

I ended up with this.
<TABLE style="WIDTH: 505pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=673 x:str><COLGROUP><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" width=118><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" width=102><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><TBODY><TR style="HEIGHT: 51pt" height=68><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 89pt; HEIGHT: 51pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=68 width=119>GEORGE J
HIGH STREET
EDINBURGH
EH1 1AA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 89pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=118>GEORGE J</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 77pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=102>HIGH STREET</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=83>EDINBURGH</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 89pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=119>EH1 1AA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=68> </TD></TR><TR style="HEIGHT: 63.75pt" height=85><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 89pt; HEIGHT: 63.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=85 width=119>GERALD HIGGINS
UNION STREET
ABERDEEN
ABERDEENSHIRE
AB12 3CD</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>GERALD HIGGINS</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>UNION STREET</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>ABERDEEN</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>ABERDEENSHIRE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>AB12 3CD</TD></TR></TBODY></TABLE>

 

[George J]

Thanks for that formula Gerald

All the address data is in the one cell and the postcode is on the last line of that cell.
I tried the formula, but there seems to be an overlap sometimes making letters at the end of the 2nd last row repeat as capitals.

You're correct about the postcodes - i meant 5 digits and a space.

Norrie, i'll take a look at the code later and see how to make it work for the formatting.

Thanks to you both.

 

[Gerald Higgins]

I tried the formula, but there seems to be an overlap sometimes making letters at the end of the 2nd last row repeat as capitals.

Can you give me a couple of examples of that please, and we might be able to tweak the formula to deal with them correctly.

 

[Gerald Higgins]

Try this version.

Basically UK postcodes can be in one of these formats -
Z9 9ZZ
Z99 9ZZ
ZZ9 9ZZ
ZZ99 9ZZ
(there are some minor exceptions, in London mainly, but I think these won't affect the way the formula works)
So, I'm assuming that the postcode will take up the final 6, 7 or 8 characters of your data.
I'm also assuming that the postcode will have a single space in the middle.
Postcode data that I've seen in the past is often poor quality, either missing these spaces entirely, or having double spaces and so on. If this applies to your data, my formula may not work correctly.
I'm also assuming that the postcode will be immediately preceded by ", " in other words a single comma and single space.
The formula works by calculating the exact position of these characters.

=IF(MID(A1,LEN(A1)-7,1)=",",PROPER(LEFT(A1,LEN(A1)-6))&RIGHT(A1,6),
IF(MID(A1,LEN(A1)-7,1)=" ",PROPER(LEFT(A1,LEN(A1)-7))&RIGHT(A1,7),
PROPER(LEFT(A1,LEN(A1)-8))&RIGHT(A1,8)))

I've tested it and it works correctly for all the postcode possibilities I've listed.