using excel text functions (specific to strings)

[Eelinla]

Hi, I am a bit stuck with my excel spreadsheet. Any help anyone could give me
would be appreciated.
Im working on moving text information (lastname,firstname) format to
separate categories while maintaining the original column. It will be used
in statistical functions after it is moved and must be formulated so that if
the information is changed (ie marriage changes a last name) it will remain
accurate and consistant.

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.)

 

[John]

have a look at Data>text to columns. This might do what you want.
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)

 

[SeaSparrow via OfficeKB.com]

In D1, enter:
=RIGHT(C1,LEN(C1)-FIND(",",C1,1))
In E1, enter:
=LEFT(C1,FIND(",",C1,1))


Then copy D1 and E1 down as far as needed.

 

[T. Valko]

For the name:

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

( in that format. one comma after city and a space after state)

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]

Hi again and thanks for the quick responses. The formulas here worked great,
but I am curious as to the specifics of the number +1255. I have a couple
ideas why you used it, but I was hoping you could give me a solid answer.

 

[T. Valko]

I am curious as to the specifics of the number +1255.

The number you're probably curious about is actually 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.

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

 

[Eelinla]

thanks so much ..very good job dummying that down into simple to understand
terms i appreciate it more than you know.

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

thanks so much ..very good job dummying that down into simple to
understand
terms i appreciate it more than you know.