Home

Immediate Excel Help Needed

%3CLINGO-SUB%20id%3D%22lingo-sub-486954%22%20slang%3D%22en-US%22%3EImmediate%20Excel%20Help%20Needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-486954%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20have%20received%20a%20work%20assignment%20that%20I%20am%20having%20difficulty%20with%2C%20and%20require%20help.%20Thank%20you%20in%20advance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20details%3C%2FP%3E%3CP%3E-I%20received%20a%20massive%20data%20set%20of%20names%20and%20addresses%3C%2FP%3E%3CP%3E-My%20job%20is%20to%20separate%20first%20and%20last%20names%20into%20separate%20columns%3C%2FP%3E%3CP%3E-The%20issue%20is%20the%20two%20people%20households%20separated%20by%20a%20%227%22%3C%2FP%3E%3CP%3E-I%20created%20a%20fake%20data%20set%20for%20data-privacy%20reasons%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-486954%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-488372%22%20slang%3D%22en-US%22%3ERe%3A%20Immediate%20Excel%20Help%20Needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-488372%22%20slang%3D%22en-US%22%3EHi%2C%20that%20is%20very%20difficult%20to%20fix%20up.%20What%20version%20of%20Excel%20are%20you%20using%20and%20have%20you%20used%20Power%20Query%20before%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-489427%22%20slang%3D%22en-US%22%3ERe%3A%20Immediate%20Excel%20Help%20Needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-489427%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F328393%22%20target%3D%22_blank%22%3E%40federips77%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20was%20not%20able%20to%20download%20you%20file%2C%20here%20is%20variant%20with%20formulas%20on%20this%20data%20set%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20381px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F110735i986E77F890FF0D7C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EFirst%2C%20add%20index%20column%20to%20your%20source%20data%20-%20add%20empty%20column%2C%201%20for%20the%20first%20record%20and%20fill%20series%20till%20end%20of%20your%20range.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOn%20another%20sheet%20also%20start%20from%20ID%20column%20and%20put%201%20in%20A2%20as%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20354px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F110737iBDE49AD519B5BF9B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIn%20A3%20add%20formula%3C%2FP%3E%0A%3CPRE%3E%3D%24A2%2B(%24A1%3D%24A2)%2BNOT(ISNUMBER(SEARCH(%22%26amp%3B%22%2CINDEX(Sheet1!B%242%3AB10%2C%24A2))))%3C%2FPRE%3E%0A%3CP%3ETo%20select%20first%20or%20second%20first%20name%20in%20B2%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(%0A%20%20%20IF(%24A2%3D%24A1%2C%0A%20%20%20%20%20%20MID(INDEX(Sheet1!B%242%3AB10%2C%24A2)%2C%0A%20%20%20%20%20%20%20%20%20SEARCH(%22%26amp%3B%22%2CINDEX(Sheet1!B%242%3AB10%2C%24A2))%2B2%2C%0A%20%20%20%20%20%20%20%20%20SEARCH(%22%23%22%2CSUBSTITUTE(INDEX(Sheet1!B%242%3AB10%2C%24A2)%2C%22%20%22%2C%22%23%22%2CLEN(INDEX(Sheet1!B%242%3AB10%2C%24A2))-LEN(SUBSTITUTE(INDEX(Sheet1!B%242%3AB10%2C%24A2)%2C%22%20%22%2C%22%22))))-%0A%20%20%20%20%20%20%20%20%20%20%20%20SEARCH(%22%26amp%3B%22%2CINDEX(Sheet1!B%242%3AB10%2C%24A2))-2)%2C%0A%20%20%20%20%20%20LEFT(INDEX(Sheet1!B%242%3AB10%2C%24A2)%2C%20SEARCH(%22%20%22%2CINDEX(Sheet1!B%242%3AB10%2C%24A2))-1))%2C%0A%22%22)%3C%2FPRE%3E%0A%3CP%3ETo%20select%20last%20name%20in%20C2%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(%0A%20%20%20RIGHT(%0A%20%20%20%20%20%20INDEX(Sheet1!B%242%3AB10%2C%24A2)%2C%0A%20%20%20%20%20%20LEN(INDEX(Sheet1!B%242%3AB10%2C%24A2))-SEARCH(%22%23%22%2CSUBSTITUTE(INDEX(Sheet1!B%242%3AB10%2C%24A2)%2C%22%20%22%2C%22%23%22%2CLEN(INDEX(Sheet1!B%242%3AB10%2C%24A2))-LEN(SUBSTITUTE(INDEX(Sheet1!B%242%3AB10%2C%24A2)%2C%22%20%22%2C%22%22))))%0A%20%20%20)%2C%22%22)%3C%2FPRE%3E%0A%3CP%3EFor%20the%20first%20address%20field%20in%20D2%3C%2FP%3E%0A%3CPRE%3E%3DINDEX(Sheet1!C%242%3AC10%2C%24A2)%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20that%20cell%20to%20the%20right%20till%20end%20of%20your%20addresses%20fields.%3C%2FP%3E%0A%3CP%3ESelect%20first%20line%2C%20let%20say%20B2%3AK2%20and%20copy%20it%20on%20the%20next%20row.%20Now%20select%20A3%3AK3%20and%20drag%20down%20till%20first%20empty%20cells%20appear%2C%20that%20will%20be%20end%20of%20your%20range%20(or%20use%20Ctrl%2BD%20to%20fill%20down).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-489506%22%20slang%3D%22en-US%22%3ERe%3A%20Immediate%20Excel%20Help%20Needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-489506%22%20slang%3D%22en-US%22%3EI%20wasn%E2%80%99t%20also%20able%20to%20download%20the%20file.%20I%20tried%20plenty%20of%20times%20but%20I%20failed.%20I%20sincerely%20admire%20the%20effort%20you%20exerted%20in%20providing%20a%20solution%20for%20the%20dilemma.%20I%20can%20only%20surmise%2C%20and%20so%20hope%2C%20that%20your%20suggested%20solution%20would%20satisfy%20the%20perceived%20requirements%20of%20the%20querist.%3C%2FLINGO-BODY%3E
Highlighted
federips77
Occasional Visitor

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.
Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies