Vba to copy address to another sheet with some capitals and comers

[tonywatsonhelp]

Hi Everyone,
I have an input sheet called "Welcome" in Range G9:G14 people add there address
its set out like this

	F	G
9	Address1	the street
10	Address2	some twon,
11	Address3	utah,
12	Address4
13	Address5
14	Zip-code	ut7 6dy
15

Now as i've tried to show here not everyone uses capitals or comers
What i would like to see is all Words Start with a capital
All Letters In Zip Code are Capitals (we can assume they get this in the correct row
all lines have a comer except lastline that is a full stop (if theres no zipcode still last line if possible

now I want it to do this whilst copying it to a new sheet
I want it copied to a sheet called "Master" starting at AF41 but heres the catch, i dont want any empty rows, so in the example above G9 would go to AF41 but G14 would be right after Utah as shown below:

Master Sheet	AF
41	The Street,	this is how the above would look when copied
42	Some Town,
43	Utah,
44	UT 6DY.
45
46

any assistance would be very much appreciated
Thanks
Tony

 

[rondeondo]

hi Tony.
For first letter caps use proper
For your zip code use upper
And for adding trailing commas you'll need something like

=proper(if(left(trim(g9,1)<>",",trim(g9)&",",trim(g9)))

 

[tonywatsonhelp]

Hi Rondeondo,
This took me a while but all worked ,
thanks
for the direction
Tony