RIGHT function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Help - 24 Mar 2008 13:06 GMT

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 to this Message

Mike H - 24 Mar 2008 13:16 GMT

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

> 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
[quoted text clipped - 6 lines]
> function wrong!). For exammple "Tuscaloosa, AL 35404" extracts as "a, AL
> 65404" Anyone show me what I am doing wrong here?! Thanks

Reply to this Message

Help - 24 Mar 2008 13:22 GMT

THank you, that worked perfectly. Can you explain what you did enlieu of
what I was doing?

> Hi,
>
[quoted text clipped - 16 lines]
> > function wrong!). For exammple "Tuscaloosa, AL 35404" extracts as "a, AL
> > 65404" Anyone show me what I am doing wrong here?! Thanks

Reply to this Message

Jarek Kujawa - 24 Mar 2008 13:27 GMT

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

Reply to this Message

Mike H - 24 Mar 2008 13:38 GMT

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

> THank you, that worked perfectly. Can you explain what you did enlieu of
> what I was doing?
[quoted text clipped - 19 lines]
> > > function wrong!). For exammple "Tuscaloosa, AL 35404" extracts as "a, AL
> > > 65404" Anyone show me what I am doing wrong here?! Thanks

Reply to this Message

Jarek Kujawa - 24 Mar 2008 13:16 GMT

"=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)

Reply to this Message

Don Guillett - 24 Mar 2008 13:19 GMT

=RIGHT(K2,LEN(K2)-FIND(",",K2,1)-1)

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> 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
[quoted text clipped - 9 lines]
> function wrong!). For exammple "Tuscaloosa, AL 35404" extracts as "a, AL
> 65404" Anyone show me what I am doing wrong here?! Thanks

Reply to this Message

Rick Rothstein (MVP - VB) - 24 Mar 2008 17:14 GMT

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

Reply to this Message