Immediate Excel Help Needed

federips77 ,

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