Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Zip Code Formatting
Hi All, I receive a monthly excel spreadsheet that contains zip codes in different formats. I'm assigning sales reps to each zip code. I ran into difficulty because this file contains both US zips (need first 5 #s of the string) and Canadian zips, which is a mix of text and numbers but always begins with a letter (e.g. "V5T 4T5"). I'm not sure how to differentiate between #s and letters. Could someone help me: 1. Format the cells that begin with a number to be the first 5 characters only. 2. Don't edit the cells that begin with letters 3. Assign cells that begin with letters as "Canada" in Cells(x,9) My code is as follows: Sub RepAssignment () x = 2 y = 9 Do While Cells(x,8).Value < "" If (Cells(x,8).Value 0) and (Cells(x,8).Value <=999) Then Cells(x,9) = "LH" If (Cells(x,8).Value 1000) and (Cells(x,8).Value <=2799) Then Cells(x,9) = "LS" ...etc... If (Cells(x,8).Value =99500) and (Cells(x,8).Value <= 99999) Then Cells(x,9) = "H" x = x + 1 Loop End Sub Thanks for your help in advance, Christine -- weeshie73 ------------------------------------------------------------------------ weeshie73's Profile: http://www.excelforum.com/member.php...o&userid=24792 View this thread: http://www.excelforum.com/showthread...hreadid=536473 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Zip Code Formatting
if isnumeric(left(cells(x,8).value,1)) then
' US Zip else ' Canadian Zip end if -- Regards, Tom Ogilvy "weeshie73" wrote: Hi All, I receive a monthly excel spreadsheet that contains zip codes in different formats. I'm assigning sales reps to each zip code. I ran into difficulty because this file contains both US zips (need first 5 #s of the string) and Canadian zips, which is a mix of text and numbers but always begins with a letter (e.g. "V5T 4T5"). I'm not sure how to differentiate between #s and letters. Could someone help me: 1. Format the cells that begin with a number to be the first 5 characters only. 2. Don't edit the cells that begin with letters 3. Assign cells that begin with letters as "Canada" in Cells(x,9) My code is as follows: Sub RepAssignment () x = 2 y = 9 Do While Cells(x,8).Value < "" If (Cells(x,8).Value 0) and (Cells(x,8).Value <=999) Then Cells(x,9) = "LH" If (Cells(x,8).Value 1000) and (Cells(x,8).Value <=2799) Then Cells(x,9) = "LS" ...etc... If (Cells(x,8).Value =99500) and (Cells(x,8).Value <= 99999) Then Cells(x,9) = "H" x = x + 1 Loop End Sub Thanks for your help in advance, Christine -- weeshie73 ------------------------------------------------------------------------ weeshie73's Profile: http://www.excelforum.com/member.php...o&userid=24792 View this thread: http://www.excelforum.com/showthread...hreadid=536473 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Zip Code Formatting | New Users to Excel | |||
zip code formatting | Excel Discussion (Misc queries) | |||
Conditonal Formatting VB Code? | Excel Programming | |||
Add Formatting Code in Macro | Excel Programming | |||
Code to maintain formatting | Excel Programming |