A cell with a list of surnames from a cell with a list of full names and random simbols.

[Konstapulkin]

Hello!

Guys, I need to do some work on a huge database and I will really apreciate if you help me to automate the most mechanical part.

What I have:

Cells with lists of fullnames in the first column ("Full list" in example). The cells contain fullnames as well as, sometimes, 'random' simbols like the ones you can see on the scrennshots ('a','b', question mark etc.)

What I need to do:
1. (2nd column - "Surnames only"): a cell with a list of surnames only (the surnames always go after the first names in the first column)
2. (3rd column - "Number of authors"): a cell with a number of surnames from the past column



Thank you in advance!
I have hundreds strokes, so it would be a really great help if I could automate this part.
фото хостинг

 

[sijpie]

Try this. GetSurnames is the macro to run


<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#007F00">'http://www.mrexcel.com/forum/excel-questions/904827-cell-list-surnames-cell-list-full-names-random-simbols.html</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> GetSurnames()<br>    <SPAN style="color:#00007F">Dim</SPAN> vIn <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, vOut <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> lR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#007F00">' create an array with the input names</SPAN><br>    vIn = Range("A1").CurrentRegion.Value<br>    <SPAN style="color:#007F00">' check if it has at least three columns</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(vIn, 2) < 3 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">ReDim</SPAN> vIn(1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(vIn, 1), 1 <SPAN style="color:#00007F">To</SPAN> 3)<br>    <br>    <SPAN style="color:#00007F">For</SPAN> lR = 2 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(vIn, 1)<br>        vIn(lR, 2) = ExtractIndivNames(CStr(vIn(lR, 1)))<br>        vIn(lR, 3) = CountNames(vIn(lR, 2))<br>    <SPAN style="color:#00007F">Next</SPAN> lR<br>    <br>    <SPAN style="color:#007F00">' now output the array to the sheet</SPAN><br>    Range("A1").Resize(UBound(vIn, 1), <SPAN style="color:#00007F">UBound</SPAN>(vIn, 2)).Value = vIn<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Function</SPAN> ExtractSurname(sFullName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> sSN <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> vSpl <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <br>    <SPAN style="color:#007F00">' split the full name (with or without 'funny' characters) using the space between words</SPAN><br>    vSpl = Split(Trim(sFullName), " ")<br>    <SPAN style="color:#007F00">' we now have an array with the different components of the full name</SPAN><br>    <SPAN style="color:#007F00">'lets start from the back and see if the last item is a 'funny'</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> i = <SPAN style="color:#00007F">UBound</SPAN>(vSpl) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">LBound</SPAN>(vSpl) <SPAN style="color:#00007F">Step</SPAN> -1<br>        <SPAN style="color:#00007F">If</SPAN> Len(vSpl(i)) > 1 And Asc(CStr(vSpl(i))) < 256 <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#007F00">' not a 'funny' so this is the surname</SPAN><br>            sSN = vSpl(i)<br>            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    <br>    ExtractSurname = sSN<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br><br><SPAN style="color:#00007F">Function</SPAN> ExtractIndivNames(sFullName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> sSN <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> vSpl <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <br>    <SPAN style="color:#007F00">' split the list of full names  using the ',' between names</SPAN><br>    vSpl = Split(Trim(sFullName), ",")<br>    <SPAN style="color:#007F00">' we now have an array with the different full names</SPAN><br>    <SPAN style="color:#007F00">' Now for each name (with or wiothout  'funnies'0 extract the surname</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> i = <SPAN style="color:#00007F">LBound</SPAN>(vSpl) <SPAN style="color:#00007F">To</SPAN> UBound(vSpl)<br>        sSN = sSN & ExtractSurname(CStr(vSpl(i)))<br>        sSN = sSN & ", "<br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    <SPAN style="color:#007F00">' now remove any trailing commas</SPAN><br>    <SPAN style="color:#00007F">Do</SPAN><br>        sSN = Left(sSN, Len(sSN) - 2)<br>    <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> Right(sSN, 2) = ", "<br>    <br>    ExtractIndivNames = sSN<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br><SPAN style="color:#00007F">Function</SPAN> CountNames(sNameString) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <br>    <SPAN style="color:#007F00">' count the commas</SPAN><br>    <SPAN style="color:#00007F">Do</SPAN><br>        j = InStr(j + 1, sNameString, ",")<br>        i = i + 1<br>    <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> j > 0<br>    CountNames = i<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br></FONT>