Forum Discussion
Immediate Excel Help Needed
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
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).
- 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.
- Hi, that is very difficult to fix up. What version of Excel are you using and have you used Power Query before?
