Can you format cells as Title Case?

George J

Well-known Member
Joined
Feb 15, 2002
Messages
959
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
 
Last edited:

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
George

It this is for a mailshot then why not use Word's mail merge?

You can format mail merge fields using switches, and I've just checked and there's a specific switch for each of the formats you seem to want.:)

Word Help said:
Capitalization formats

<TABLE cellSpacing=4 cellPadding=4 cols=2><TBODY><TR vAlign=top><TH width="23%">This switch</TH><TH width="77%">Capitalizes</TH></TR><TR vAlign=top><TD class=TT width="23%">\* Caps</TD><TD class=TT width="77%">The first letter of each word. For example,
{ FILLIN "Type your name:" \* Caps } displays "Julie Tanner" even if the name is typed in lowercase letters.

</TD></TR><TR vAlign=top><TD class=TT width="23%">\* FirstCap</TD><TD class=TT width="77%">The first letter of the first word. For example,
{ COMMENT \* FirstCap } displays "Weekly report on sales".

</TD></TR><TR vAlign=top><TD class=TT width="23%">\* Upper</TD><TD class=TT width="77%">All letters. For example, { QUOTE "word" \* Upper } displays "WORD".</TD></TR><TR vAlign=top><TD class=TT width="23%">\* Lower</TD><TD class=TT width="77%">None of the result; all letters are lowercase. For example, { FILENAME \* Lower } displays "weekly sales report.doc". Note This switch has no effect if the entire field that contains the switch is formatted as small capital letters.


</TD></TR></TBODY></TABLE>
 

George J

Well-known Member
Joined
Feb 15, 2002
Messages
959
Hi Norie, thanks for posting.

I'm pulling the data from a website and all the letters are capitals.
We were going to do the mail merge in word, but they wanted the text to be both upper and lower case. This would be fine except for the postcode which we don't want any lower case letters in.

Unfortunately the address field has both the address and postcode so it will come out as Ab12 3cd

I think i will need to adjust it to Title Case which is easy in word, but then make the last line of each cell all capitals. I coded on word once or twice, but have no idea how to do this.
 

BGY23

Well-known Member
Joined
Aug 13, 2008
Messages
515
=proper(cell address) will turn the text in the cell address to Capital at start of every word and lower case rest.

=Upper(cell address) will turn the text in cell address to capitals.
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115

ADVERTISEMENT

Is ALL the address information in a single cell ?
If not, you can probably use a formula to determine whether or not a cell is a postcode, and if it is, leave it as is, and if not, use the PROPER function.
 

George J

Well-known Member
Joined
Feb 15, 2002
Messages
959
Thanks, but i still don't know how to tackle this.

The Full address is the street, town and postcode (all in the one cell). This means that the Street and Town should be 'proper', but the Postcode should be upper.

I think the only way to tackle this is in code, but i can't think of the right way to go about it. Was kinda hoping this question had been asked before :)
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115

ADVERTISEMENT

Is there any way that you can consistently identify the postcode ?
For instance, does the postcode always begin at a specific character number ?
Are there always three commas before the postcode ?
Are you only dealing with Aberdeen postcodes ? (probably not, but worth a shot ;) )
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
George

My bad I thought you had things in separate cells.

Is there no way you could do that? Is the data delimited in anyway?

How are you getting the data in the first place?

Perhaps you could post some example's.
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Try this.
It relies on the assumption that UK postcodes are no longer than 8 characters (AB12 3CD), although they could be as short as 6 (G1 2AB for example).

It's not perfect (sometimes adds in extra spaces) but maybe acceptable.
Test it thoroughly....

Code:
=PROPER(LEFT(A1,LEN(A1)-8))&MID(H1,LEN(H1)-8,9)

If the extra spaces are a problem, this can be refined.
 

George J

Well-known Member
Joined
Feb 15, 2002
Messages
959
The only way i can identify the postcode is that it is always on the last line. There is nothing else as there are variable amounts of lines depending on the address. Some have just the street, town and postcode - others have the property name, street, town, district, region and psotcode.

The data is pulled from a web page table. I then extract that into excel. It just copies the cell from the web page so there is no way to split it into address and postcode. Some postcodes have 5 letters, others have up to 8 (with a space seperating part of it)

I guess i could try to write some code to look for the last 'return' and anything after this should be capitals. It would be easier in word as it can do an entire column in Title Case then find the last 'return' in each cell adn anything after that character change to uppercase. Just got no idea how to do that in word :(

GEORGE J
HIGH STREET
EDINBURGH
EH1 1AA

GERALD HIGGINS
UNION STREET
ABERDEEN
ABERDEENSHIRE
AB12 3CD
 

Watch MrExcel Video

Forum statistics

Threads
1,123,110
Messages
5,599,773
Members
414,336
Latest member
Nicolas2465

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top