Immediate Excel Help Needed

Copper Contributor

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

 

3 Replies
Hi, that is very difficult to fix up. What version of Excel are you using and have you used Power Query before?

@federips77 ,

 

I was not able to download you file, here is variant with formulas on this data set

image.png

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

image.png

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

I wasn’t also able to download the file. I tried plenty of times but I failed. I sincerely admire the effort you exerted in providing a solution for the dilemma. I can only surmise, and so hope, that your suggested solution would satisfy the perceived requirements of the querist.