• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

data sorting ....

R

ramsy

New Member
well sorry if the title seems a bit misleading but i couldnt find a better one. My problem in excel is that there is a huge list of names and their corresponding ranks in pdf format. When I copy the data to excel sheet all data is shown in one column. Now the problematic part is taking out the serial no, rank and the name and placing them in different columns.


Please help me with this.
 
Hi ramsy,


Can you upload the sample data? Have you tried to used 'Text to Column' Option available in Excel?? That will split data in to multiple columns and hence you can find the serial no.


Regards,

Faseeh
 
hi Faseeh,


I tried uploading the excel file but i cant find upload option. Can you pls direct me ...
 
also i might give you an example :


Raw data

SR1234567 Stephen Hawking 123 231

SR1234568 Christopher Nolan 451 578


Now I want the SRXXXXXXX in one column, the name in one column and the two ranks in two different columns ...


i tried the text to column option but couldnt get it right... separating the SR number was easy but the ranks part was not... also i might be doing it wrong...


Kindly help with this and also thank you for introducing me to Text to Column option


Regards
 
Hi ramsy,


Few more questions:


-Will you serial no always consists of this many characters?

-The names have only two parts?

-Why there is a space in corresponding rank?


Regards,

Faseeh
 
Hi ramsy,


Assuming that you data in present in A1 (
Code: 
SR1234567 Stephen Hawking 123 231) Enter this:


For Serial No.: [code]=LEFT(A1,10)

For Name: =MID(A1,10,LEN(A1)-16)

For Rank: =RIGHT(A1,6)[/code]


Regards,

Faseeh
 
the serial number will always be of constant string length .. hence easy to sort. But the name might not be two words always might vary from 1 to 4.There are actually two ranks. The first rank is the waitlist rank and the second rank is merit rank. and the difference between the two numbers is not constant (icing on the cake :))...


link to the source i am referring to :

http://www.mdi.ac.in/pdf/PGP%20WAIT%20LIST%202012-14.pdf
 
Hi ramsy!!


Well i finally found one!! :) To break the ice on the cake :D


For Serial No:

Code: 
=LEFT(A1,10)


For Name:

[code]=MID(A1,10,LEN(A1)-9-LEN(D1&E1))


For Merit Rank:

=MID($A1,LARGE(((MID($A1,ROW(INDIRECT("1:"&LEN($A1))),1))=" ")*ROW(INDIRECT("1:"&LEN($A1))),2),(LARGE(((MID($A1,ROW(INDIRECT("1:"&LEN($A1))),1))=" ")*ROW(INDIRECT("1:"&LEN($A1))),1))-(LARGE(((MID($A1,ROW(INDIRECT("1:"&LEN($A1))),1))=" ")*ROW(INDIRECT("1:"&LEN($A1))),2)))


For Wait-list No:

=MID($A1,LARGE(((MID($A1,ROW(INDIRECT("1:"&LEN($A1))),1))=" ")*ROW(INDIRECT("1:"&LEN($A1))),1),256)[/code]


Formula for merit rank & waiting list to be entered with Ctrl+Shift+Enter (Array Formulas).


I assumed your data is in column A while these four parts are in B, C, D and E Respectively.


PS: Fellows here could come up with 'Smarter' solutions, so stay in touch with this thread. (I believe if we/one can understand the logic underneath, this could be a more universal solution for such "split-text" problems..)


Regards,

Faseeh
 
Hi ,


Alternatives to Faseeh's formulae for (3) and (4) , courtesy Harlan Grove and others :


3. =LEFT(MID(A1,LARGE(IF(MID(A1,seq,1)=" ",seq),2)+1,1024),FIND(" ",MID(A1,LARGE(IF(MID(A1,seq,1)=" ",seq),2)+1,1024))-1)


entered as an array formula ( using CTRL SHIFT ENTER ) , where seq is a range name defined as :


=ROW(INDIRECT("1:1024"))


4. =RIGHT(A1,LEN(A1)-FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))


The construct :


LEN(A1)-LEN(SUBSTITUTE(A1," ",""))


is a very useful one , and worth remembering ; the string in A1 is modified by replacing all spaces with null characters ( SUBSTITUTE(A1," ","") ) , the length of this modified string is taken , and subtracted from the length of the original string ; this will give the number of spaces in the original string.


In fact , the formula in (3) above , uses a construct , which is also worth remembering :


=MID(A1,LARGE(IF(MID(A1,seq,1)=" ",seq),1)+1,1024)


entered as an array formula , will return the answer to (4).


Try changing the 1 to 2 to 3 ... in the above construct e.g.


=MID(A1,LARGE(IF(MID(A1,seq,1)=" ",seq),2)+1,1024)


=MID(A1,LARGE(IF(MID(A1,seq,1)=" ",seq),3)+1,1024)


and see what you get. Try replacing LARGE with SMALL and see what you get.


Narayan
 
Hi All,


Thanks to NARAYANK991 who actually pointed out that named ranges could be used in this formula, i tried today with that and the formula is my third last post seems much slimmer:


For Part # 3:

Code: 
=MID($A1,LARGE(((MID($A1,seq,1))=" ")*seq,2),(LARGE(((MID($A1,seq,1))=" ")*seq,1))-(LARGE(((MID($A1,seq,1))=" ")*seq,2)


For Part # 4:

[code]=MID($A1,LARGE(((MID($A1,seq,1))=" ")*seq,1),256)


...where seq = =ROW(INDIRECT("1:"&1024))[/code]


Thanks NARAYANK991 for teaching me this trick!


Regards,

Faseeh
 
You must log in or register to reply here.
Back
Top