|
Two Separate Questions Regarding Two Different Columns
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. -- Templar |
Two Separate Questions Regarding Two Different Columns
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: 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. -- Templar |
Two Separate Questions Regarding Two Different Columns
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 On Oct 2, 12:18*pm, Templar wrote: 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. -- Templar |
Two Separate Questions Regarding Two Different Columns
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. Templar wrote: 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. -- Templar -- Dave Peterson |
| All times are GMT +1. The time now is 05:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com