1. ## Seperating Names (2002)

I know the Lounge can help here AGAIN!!
I have some old spreadsheets (that were put together by someone with even less knowledge than me!), with a large list of names all in one column - i.e. Smith Bill. I have two options 1) put the Bill into A1 and the Smith into B1 or 2) turn them around to Bill Smith in A1. Any ideas Guru's

<img src=/S/aussie.gif border=0 alt=aussie width=21 height=22> Dean

2. ## Re: Seperating Names (2002)

<img src=/S/hello.gif border=0 alt=hello width=25 height=29> Dean

OK basically you should check the Text functions to deal with this issue.

For example:

=LEFT(A1,FIND(" ",A1,1)-1) will get you what is to the Left of the Space <font color=red>Smith</font color=red>

=RIGHT(A1,FIND(" ",A1,1)-2) will get you what is to the Right of the Space <font color=red> Bill</font color=red>

Now how these two functions work:

Left needs 2 arguments From what string, A1 has the names so it will use that, and how many characters. Well here comes the Find, which takes three arguments:
Find what, a space, in what string, A1 and starting from what character, First = 1. That will yield a number for the Left function and then we deduct the space and that is where the -1 comes in

Right does the same, except from the right, and the -2 is because there is the Space and your next character.

test these, and also look at the MID functions.

<font color=red> Warning:</font color=red> if there are multiple spaces in the name, such as Mc Adam, or what have you in the French language you will need to use more complex functions.

Hope this helps.

Wassim

3. ## Re: Seperating Names (2002)

Thanks very much Wassim - will give it a shot!!

<img src=/S/aussie.gif border=0 alt=aussie width=21 height=22> Dean

4. ## Re: Seperating Names (2002)

Another thing to check is the Data, text-to-columns menufunction.

5. ## Re: Seperating Names (2002)

Wassam,

The left function works fine however the right is returning variable results ie Atkin Nicholas is returning 'olas Atkin'

Any thoughts?

<img src=/S/aussie.gif border=0 alt=aussie width=21 height=22> Dean

6. ## Re: Seperating Names (2002)

Try

=MID(A2,FIND(" ",A2,1)+1,9999)

(the 9999 is just a number longer than your cell values are going to be; any other large number would do)

7. ## Re: Seperating Names (2002)

<img src=/S/hello.gif border=0 alt=hello width=25 height=29> Dean

OK try this one for size:

=RIGHT(A1,LEN(A1)-FIND(" ",A1,1))

This finds the length of whatever is in A1, so eg if you have Bob it will give you 3. Mind you it also counts spaces.

Find will give you the position of the space, so we will subtract that from the length, and get to the character just after the space.

Now I know that other members of the Lounge gave you excellent advice, I wanted to give you some info maybe you can use it in future projects.

Wassim is my name <img src=/S/drop.gif border=0 alt=drop width=23 height=23>

8. ## Re: Seperating Names (2002)

I think you have what you need, but for future reference, http://www.cpearson.com/excel/FirstLast.htm.

9. ## Re: Seperating Names (2002)

And lastly (really?) and a bit off-topic, if you think that mailing labels etc generated from lists in the form JOE BLOGGS are ugly and look like they've been done by machine(!), the =PROPER function will convert them to Joe Bloggs and I leave you to work out how to handle the resulting Fred Mctavish and his Glaswegian chums. (And then tell me, please!)

10. ## Re: Seperating Names (2002)

<img src=/S/hello.gif border=0 alt=hello width=25 height=29> John

I was actually hoping that soon I will have enough time to write a VBA function to handle all these special cases. You do have Arabic, French, German, and Irish/Scotish names that fall into these special cases, and they are all known.

Would you like to be involved in writing this function?

Wassim

11. ## Re: Seperating Names (2002)

The best way to handle this is to NEVER put the names in upper case in a database. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> Put them in properly. If you need to display/print/compare as all uppercase, then use the function (UCASE in VB) to change "on the fly".

Steve

12. ## Re: Seperating Names (2002)

<!profile=MarkD>MarkD<!/profile> posted a function that handles some exceptions in <post#=309766>post 309766</post#>

13. ## Re: Seperating Names (2002)

Wassim,
I'm flattered to be asked but a) concerning Arabic, my computer only writes forwards ie left to right and [img]/forums/images/smilies/cool.gif[/img] concerning vba, my knowledge is such that the world's smallest postage stamp is too vast a canvas on which to describe the sum total of All I Know.
My workaround is to ensure that anything I put into a list in the McDonald, MacDonald, d'Artagnan, d'Arcy etc departments is in the correct case (for me) in the first place.
How would you propose to handle amibidextrous names like Macdonald or FitzWarren / Fitzwarren? And the poet WALTER DE LA MERE?