Name cosolidation

[bebo021999]

Hi every body, could you please help me

I have list of Full Name in column A and wanna get result in column B:

Column A / Column B

Nguyen Ngoc Tuan Anh / AnhNNT


Doan Thu Nguyet / NguyetDT

Tran Viet / VietT

What formula can I use? (No VBA)

Thanks in advance

[Richard Buttrey]

Why the reluctance to use VBA which I suspect in this case would be much easier?

Regards

[arlu1201]

Yes, i agree. I can't think of a direct formula that will do this. Even if there is one, it will be excessively complicated. Why not use VBA?

[ssu95bm]

Yes, this is better in vba. But it can be done without.

For the easiest example, Tran Viet / VietT, you can use this (where a2 is the cell containing the full name):

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))&LEFT(A2,1)

For the hardest example, Nguyen Ngoc Tuan Anh / AnhNNT, it is more complicated as you have to find successive spaces (in this case A8 contains the full name):

RIGHT(A8,LEN(A8)-SEARCH(" ",A8,(SEARCH(" ",A8,SEARCH(" ",A8,1)+1))+1)) & LEFT(A8,1) & MID(A8,SEARCH(" ",A8,1)+1,1) & MID(A8,SEARCH(" ",A8,SEARCH(" ",A8,1)+1)+1,1)

See the attached example spreadsheet.

Now the issue is that, if it doesn't find extra spaces, you will get an error. To get around that you could either concoct a huge IF statement using the ISERROR function to find out how many spaces you have, or have separate columns to find each successive space and therefore initial, then a final column with an IF statement using the ISERROR function to determine how many of your initials you concatenate with the final name.

Alternatively you can just visually determine which names need which version of the formula...

I hope that helps...
ssu95bm

[ssu95bm]

I've just thought of an even easier way to do this.

Export your full name column (just that column) as text. The re-import it, as delimited data, with the space as the delimiter. This will then allow you to spread the different parts of the name accross a set of columns, and it is then easy to do the consolidation.

For example, cells A13-D13 contain the four possible parts of the name, then E13 would look like this:

=IF(D13<>"",D13&LEFT(A13,1)&LEFT(B13,1)&LEFT(C13,1),IF(C13<>"",C13&LEFT(A13,1)&LEFT(B13,1),B13&LEFT(A13,1)))

Using the if statement takes account of how many parts of the name there will be (and that the last name will be in either B13, C13 or D13 accordingly).

See revised example worksheet.

Hope that helps!
ssu95bm

[arlu1201]

If your query is resolved, please mark the Thread as Solved. Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes.

If you found any post helpful, please rate it accordingly.