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
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
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies