using excel text functions (specific to strings)

following is an example:

column C contains the lastname,firstname in that format. no space just the
comma.
I need to move the first name to column D
I need to move the last name to column E

I also need to format the city, state zip in the same manner

Example:
column F contains city, state zip ( in that format. one comma after city and
a space after state)
I need to move the the state to column G
I need to move the zip to column H ( I assume using the =Right F3,5 command
should work for this one.)

Then copy D1 and E1 down as far as needed.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200704/1

C3 = lastname,firstname

First name:

=MID(C3,FIND(",",C3)+1,255)

Last name:

=LEFT(C3,FIND(",",C3)-1)

For the address:

F3 = city, state zip

You also have a space after the comma.

For the state:

=TRIM(SUBSTITUTE(MID(F3,FIND(",",F3)+1,255),H3,""))

I'm using the TRIM function just in case there may or may not be a space
after the comma.

For the zip code:

=RIGHT(F3,5)

I'm assuming there are no zip+4's

Biff

"Eelinla" <Eel...@discussions.microsoft.com> wrote in message
news:2DFF180B-72E1-4FEC...@microsoft.com...

The number you're probably curious about is actually 255.

>> =MID(C3,FIND(",",C3)+1,255)

1,255

does not mean one thousand two hundred fifty-five.

Those are two separate numbers. 1 *and* 255. The comma is not a thousands
separator. It's an argument separator. The above formula has 3 main
arguments. The comma tells Excel where one argument ends and the next one
begins.

C3 is the first argument
FIND(",",C3)+1 is the second argument
255 is the third argument

The FIND function has 2 of its own arguments.

>> =MID(C3,FIND(",",C3)+1,255)

The first argument, C3, is the data we want to manipulate. We want to
extract a portion of that data.

The second argument tells Excel what position number to use as the starting
point for the data we want to extract. FIND is looking for the position of
the comma between the lastname and firstname:

Smith,John

The comma is in position 6 (the 6th character)

We want to extract the first name from the string. Since the first name is
immediately after the comma which is the 6th character we need to add 1 to
tell Excel we want to start at the 7th character.

The third argument, 255, tells Excel how many characters we want to extract.
Since we don't know how long the firstname will be we use an arbitrarily
large number that is pretty much guaranteed to cover the length of the
firstname.

Since the firstname is not 255 characters long Excel extracts whatever is
there.

So, to boil it all down in plain English:

Extract 255 characters starting at the 7th character of the string in cell
C3.

Biff

news:7315A1FC-0031-4204...@microsoft.com...

Biff

news:758FE2AD-2E32-4E9B...@microsoft.com...