Excel Help Needed

ericd102

I had the answer to this over at RM, but that sit isn't working right now.

I have a column of names in Excel, in the form "first last" or "first middle initial last" (they are mixed, not all names have a middle initial). I need to sort by last name. How can I do this?

Someone (sundevil? maybe?) had created a two-step way (using two forumlas) to do this. The formulas were both pretty complex. One had to be put in one column, and the other in the next column. The second formula referenced the first.


Anyways, this worked perfectly, but I didn't save (like an idiot) and now I can't get it to work because I can't get on RM to get the formulas.

Does anyone know how to do this, or does any RMer have that page still in their history/Tempororay files and could look it up for me? It would be immensley helpful.


Thanks,
Eric

DarkStar

you can use the following if the middle initials are followed by a period...

first we're going to split the first names and any middile initials to their own column...second we'll spilt out the last names to a separate column...third we'll merge the data in both newly created columns so that the last name appears first and is separated from the first name (plus any middle initials) with a coma.

So the data will start out as

edward l. severson

and end up as

severson, edward l.

Step 1: To split the first name and middle initials into a cell separate from the last name, copy/paste this formula to whatever column you want the first names plus any middle initials that may exist:

=IF(ISERROR(FIND(".",A1,1)),LEFT(A1,FIND(" ",A1,1)-1),LEFT(A1,FIND(".",A1,1)))

Step 2: To split the last names into their own cells, copy/paste this formula to whatever column you want the last name to appear in:

=IF(ISERROR(FIND(".",A1,1)),RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),RIGHT(A1,LEN(A1)-FIND(".",A1,1)-1))

Step 3: Time to merge the names again to one cell so that the last name appears first and is separated from the first name (plus any middle initials) with a coma...copy/paste this formula to whatever column you want the merged data to appear in:

=C1&", "&B1

ds

The Juggler

Hello nerds,

I am trying to use the below formula but it's too large, apparently.  I keep getting the below error:

This formula uses more levels of nesting than you can use in the current file format

Can anyone tell me what I need to do to shorten it? And there's 20 bucks in it for yourself, if you'll just do the work for me. 



=IF(B8<6.99,B8*0,IF(B8<7.99,B8*100,IF(B8<8.99,B8*150,IF(B8<9.99,B8*200,IF(B8<10.99,B8*250,IF(B8<11.99,B8*300,IF(B8<12.99,B8*375,IF(B8<13.99,B8*500,IF(B8<14.99,B8*566,IF(B8<15.99,B8*576,IF(B8<16.99,B8*585,IF(B8<16.99,B8*585,IF(B8<17.99,B8*639,IF(B8<18.99,B8*647,IF(B8<19.99,B8*715,IF(B8<20.99,B8*722,IF(B8<21.99,B8*728,IF(B8<22.99,B8*733,IF(B8<23.99,B8*738,IF(B8<24.99,B8*743,IF(B8<25.99,B8*747,IF(B8<26.99,B8*751,IF(B8<27.99,B8*755,IF(B8<28.99,B*758,IF(B8<29.99,B*761,IF(B8<30.99,B8*764,IF(B8<31.99,B8*767,IF(B8<32.99,B8*769,IF(B8<33.99,B8*769,IF(B8<34.99,B8*774,IF(B8<35,B8*776))))))))))))))))))))))))))))))+2500

pantelis79

The function you posted is missing a parenthesis before "+2500". There are 31 parentheses opening, but only 30 closing.

F Me In The Brain

 

The Juggler

pantelis79 said:

The function you posted is missing a parenthesis before "+2500". There are 31 parentheses opening, but only 30 closing.

Thanks. I will add that. It’s still too long though. Can you fix that problem?

pantelis79

It works in my computer . What exactly do you want to do? Could you post a table of values or something like that? I see that "16.99" is repeated in the function - it would be easier to have the initial problem here .