Apr 26 2019 10:27 AM
Hello, I have received a work assignment that I am having difficulty with, and require help. Thank you in advance!
The details
-I received a massive data set of names and addresses
-My job is to separate first and last names into separate columns
-The issue is the two people households separated by a "7"
-I created a fake data set for data-privacy reasons
Apr 26 2019 05:30 PM
Apr 27 2019 07:46 AM
I was not able to download you file, here is variant with formulas on this data set
First, add index column to your source data - add empty column, 1 for the first record and fill series till end of your range.
On another sheet also start from ID column and put 1 in A2 as here
In A3 add formula
=$A2+($A1=$A2)+NOT(ISNUMBER(SEARCH("&",INDEX(Sheet1!B$2:B10,$A2))))
To select first or second first name in B2
=IFERROR( IF($A2=$A1, MID(INDEX(Sheet1!B$2:B10,$A2), SEARCH("&",INDEX(Sheet1!B$2:B10,$A2))+2, SEARCH("#",SUBSTITUTE(INDEX(Sheet1!B$2:B10,$A2)," ","#",LEN(INDEX(Sheet1!B$2:B10,$A2))-LEN(SUBSTITUTE(INDEX(Sheet1!B$2:B10,$A2)," ",""))))- SEARCH("&",INDEX(Sheet1!B$2:B10,$A2))-2), LEFT(INDEX(Sheet1!B$2:B10,$A2), SEARCH(" ",INDEX(Sheet1!B$2:B10,$A2))-1)), "")
To select last name in C2
=IFERROR( RIGHT( INDEX(Sheet1!B$2:B10,$A2), LEN(INDEX(Sheet1!B$2:B10,$A2))-SEARCH("#",SUBSTITUTE(INDEX(Sheet1!B$2:B10,$A2)," ","#",LEN(INDEX(Sheet1!B$2:B10,$A2))-LEN(SUBSTITUTE(INDEX(Sheet1!B$2:B10,$A2)," ","")))) ),"")
For the first address field in D2
=INDEX(Sheet1!C$2:C10,$A2)
and drag that cell to the right till end of your addresses fields.
Select first line, let say B2:K2 and copy it on the next row. Now select A3:K3 and drag down till first empty cells appear, that will be end of your range (or use Ctrl+D to fill down).
Apr 27 2019 08:47 AM