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

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
3,194
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Everyone,
I have an input sheet called "Welcome" in Range G9:G14 people add there address
its set out like this
FG
9Address1the street
10Address2some twon,
11Address3utah,
12Address4
13Address5
14Zip-codeut7 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 SheetAF
41The Street,this is how the above would look when copied
42Some Town,
43Utah,
44UT 6DY.
45
46

any assistance would be very much appreciated
Thanks
Tony
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
hi Tony.
For first letter caps use proper
For your zip code use upper
And for adding trailing commas you'll need something like
Excel Formula:
=proper(if(left(trim(g9,1)<>",",trim(g9)&",",trim(g9)))
 
Upvote 0
Hi Rondeondo,
This took me a while but all worked ,
thanks
for the direction :)
Tony
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

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
Back
Top