Two Separate Questions Regarding Two Different Columns

T

Templar

Could someone please provide me with a step by step procedure for the
following "column problems"?

In column C I have the Owner's Name beginning with
the last name, then a comma and then the first name(s).
I would like to put the last name to the left of the comma in
one column and the first name(s) to the right of the comma in
a separate column.

Further down below is a different column problem: COLUMN D

COLUMN C
Owner Name
POWERS, DAVID
KAZYAK, SHANNON & BRIAN
BAYER, EMILY
OBERHOLTZER, KARLA
GILBERT, ELYSE/BRIAN
HILL, DAVID
SICILIANO, DIANA
ZUCKER, JAMES, JOY
MURPHY, WILLIAM
LUPO, DAVID
MENDEL, JONATHAN
YATES, SUSAN
DEVONE, CARRIE
HAYS, RYAN
PHIFER, ROY & KAREN
FARLEY, BARBARA
MORGAN, SHARON & JIM


Below in COLUMN D, I would like to put the number itself in one column and
everything to the right of the number in its own column

COLUMN D
StreetAddress
1122 N LOMBARD AVE
705 N HUMPHREY AVE
1221 N RIDGELAND AVE
1021 N EUCLID AVE
1131 WOODBINE AVE
521 S GROVE AVE
224 LAKE ST
1032 N EAST AVE
332 S EUCLID
538 N EAST AVE
112 S TAYLOR
1175 HOME AVE
42 LAKE
12 W HARRISON
833 N EUCLID AVE
825 WOODBINE AVE
1229 HAYES AVE

Thank you.
 
S

Stefi

last name: =LEFT(C2,SEARCH(",",C2)-1)
first name: =RIGHT(C2,LEN(C2)-SEARCH(",",C2)-2)
number: =LEFT(D2,SEARCH(" ",D2)-1)
street: =RIGHT(D2,LEN(D2)-SEARCH(" ",D2)-2)

Regards,
Stefi

„Templar†ezt írta:
 
P

Pete_UK

Use these two formulae to extract the names (the cells are what I
used):

F2: =RIGHT(C2,LEN(C2)-FIND(",",C2)-2)
G2: =LEFT(C2,FIND(",",C2)-1)

The following two formulae can be used on the address:

H2: =LEFT(D2,FIND(" ",D2)-1)
I2: =TRIM(RIGHT(D2,LEN(D2)-FIND(" ",D2)))

For some reason the TRIM function does not seem to be removing leading
spaces for some of the address (where you have double-spaces in column
D). I've also tried this instead of I2:

=TRIM(SUBSTITUTE(D2,H2&" ",""))

but that also leaves leading spaces - can anyone else advise (none of
the double spaces contain code 160).

Hope this helps.

Pete
 
D

Dave Peterson

You can use a formula like:
=LEFT(C1,FIND(",",C1)-1)
to get the first portion of the string

And
=TRIM(MID(C1,FIND(",",C1)+1,255))
to get the second portion

======
For the address, you can just change the "," to " "
=LEFT(D1,FIND(" ",D1)-1)
and
=TRIM(MID(D1,FIND(" ",D1)+1,255))

This assumes that there's always a comma/space separating the fields.
 
Top