Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
RIGHT function
In one column I have City State and Zip info. In a nearby column I am
attempting to extract data simply the state and zip. For example in the first column I have "Clanton, AL 35045". I am attempting to simply put "AL 35045" In a second column. The function I am using is "=RIGHT(C2,FIND(",",C6,1)". The way I understand this is that I am telling it to extract everything after the comma. The function works for all information in cities with seven letters (like Clanton). For all cities with more or less letters it doesn't function properly (more likely I wrote the function wrong!). For exammple "Tuscaloosa, AL 35404" extracts as "a, AL 65404" Anyone show me what I am doing wrong here?! Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
RIGHT function
Hi,
Try this =MID(A1,FIND(",",A1,1)+1,LEN(A1)) or if that's always a space after the comma try this =MID(A1,FIND(" ",A1,1)+1,LEN(A1)) Mike "Help" wrote: In one column I have City State and Zip info. In a nearby column I am attempting to extract data simply the state and zip. For example in the first column I have "Clanton, AL 35045". I am attempting to simply put "AL 35045" In a second column. The function I am using is "=RIGHT(C2,FIND(",",C6,1)". The way I understand this is that I am telling it to extract everything after the comma. The function works for all information in cities with seven letters (like Clanton). For all cities with more or less letters it doesn't function properly (more likely I wrote the function wrong!). For exammple "Tuscaloosa, AL 35404" extracts as "a, AL 65404" Anyone show me what I am doing wrong here?! Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
RIGHT function
"=RIGHT(C2,FIND(",",C6,1)"? 1) why are you cutting from C2 and based on what you find after a comma in C6? 2) try: =RIGHT(C2,LEN(C2) - FIND(",",C2,1)-1) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
RIGHT function
=RIGHT(K2,LEN(K2)-FIND(",",K2,1)-1)
-- Don Guillett Microsoft MVP Excel SalesAid Software "Help" wrote in message ... In one column I have City State and Zip info. In a nearby column I am attempting to extract data simply the state and zip. For example in the first column I have "Clanton, AL 35045". I am attempting to simply put "AL 35045" In a second column. The function I am using is "=RIGHT(C2,FIND(",",C6,1)". The way I understand this is that I am telling it to extract everything after the comma. The function works for all information in cities with seven letters (like Clanton). For all cities with more or less letters it doesn't function properly (more likely I wrote the function wrong!). For exammple "Tuscaloosa, AL 35404" extracts as "a, AL 65404" Anyone show me what I am doing wrong here?! Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
RIGHT function
THank you, that worked perfectly. Can you explain what you did enlieu of
what I was doing? "Mike H" wrote: Hi, Try this =MID(A1,FIND(",",A1,1)+1,LEN(A1)) or if that's always a space after the comma try this =MID(A1,FIND(" ",A1,1)+1,LEN(A1)) Mike "Help" wrote: In one column I have City State and Zip info. In a nearby column I am attempting to extract data simply the state and zip. For example in the first column I have "Clanton, AL 35045". I am attempting to simply put "AL 35045" In a second column. The function I am using is "=RIGHT(C2,FIND(",",C6,1)". The way I understand this is that I am telling it to extract everything after the comma. The function works for all information in cities with seven letters (like Clanton). For all cities with more or less letters it doesn't function properly (more likely I wrote the function wrong!). For exammple "Tuscaloosa, AL 35404" extracts as "a, AL 65404" Anyone show me what I am doing wrong here?! Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
RIGHT function
LEN function shows the length of a cell
i.e. using it in yr formula you base the result on the cell's length otherwise you always cut the same number of characters from the cell |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
RIGHT function
You were finding the comma correctly but but then taking that many characters
from the right so your formula only worked if the comma was central i.e. 8 from the left an 8 from the right. The correct way to use right is shown in your other replies but I chose to use MID i.e. find the comma in the same way as you did and then take every thing form that mid position(+1). Using len(a1) ensured I took enough characters. Mike "Help" wrote: THank you, that worked perfectly. Can you explain what you did enlieu of what I was doing? "Mike H" wrote: Hi, Try this =MID(A1,FIND(",",A1,1)+1,LEN(A1)) or if that's always a space after the comma try this =MID(A1,FIND(" ",A1,1)+1,LEN(A1)) Mike "Help" wrote: In one column I have City State and Zip info. In a nearby column I am attempting to extract data simply the state and zip. For example in the first column I have "Clanton, AL 35045". I am attempting to simply put "AL 35045" In a second column. The function I am using is "=RIGHT(C2,FIND(",",C6,1)". The way I understand this is that I am telling it to extract everything after the comma. The function works for all information in cities with seven letters (like Clanton). For all cities with more or less letters it doesn't function properly (more likely I wrote the function wrong!). For exammple "Tuscaloosa, AL 35404" extracts as "a, AL 65404" Anyone show me what I am doing wrong here?! Thanks |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
RIGHT function
Assuming you only have 5-digit Zip Codes (never the 9-digit version with the
dash in it), and the fact that postal state abbreviations are always 2-character abbreviations, couldn't you just use this? =RIGHT(C2, 8) Rick "Help" wrote in message ... In one column I have City State and Zip info. In a nearby column I am attempting to extract data simply the state and zip. For example in the first column I have "Clanton, AL 35045". I am attempting to simply put "AL 35045" In a second column. The function I am using is "=RIGHT(C2,FIND(",",C6,1)". The way I understand this is that I am telling it to extract everything after the comma. The function works for all information in cities with seven letters (like Clanton). For all cities with more or less letters it doesn't function properly (more likely I wrote the function wrong!). For exammple "Tuscaloosa, AL 35404" extracts as "a, AL 65404" Anyone show me what I am doing wrong here?! Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |