Splitting a full name to 2 cells

[soranz]

Jenny M Giles
Alen Wood

Hello. I am trying to build a function that takes a string and breaks apart a cell if it has more than 1 name in it and place the 2nd part of the name into the adjacent column

	Jenny	M Giles
	Alen	Wood

Please Login or Register  to view this content.

I'm stuck with the ActiveCell stuff...I've tried lots of combinations! Please help thanks!!!

[p24leclerc]

A user defined function can't directly change cells but here is a work around found at http://stackoverflow.com/questions/1...fined-function
Here is what you need to do:
Here is your function in a module. Do not forget to declare the Public variable as the Worksheet Event will use them.:

Please Login or Register  to view this content.

And here is the Event you need at the worksheet level:

Please Login or Register  to view this content.

You'll remark that I don't use the wordcount sub but the INSTR function to find a space in the name parameter.
Hope this helps

[Fettertiger]

Hi soranz,

I probably would not use a fuction, as you need to results - Firstname and lastname, but this depends on your file

You could use the following Sub instead. I assume the names are in column A (starting in row 2), and you would like to split them to column A and B.

Please Login or Register  to view this content.

Let me know if this is what you need.

Theo

[AlKey]

Or you can also use formula

in B1 and pull formula to the right and down.

=TRIM(MID(SUBSTITUTE(SUBSTITUTE("|"&$A1," ","|",1),"|",REPT(" ",125)),125*COLUMNS($A:A),125))

Row\Col	A	B	C
1	Jenny M Giles	Jenny	M Giles
2	Alen Wood	Alen	Wood

[xladept]

It may not be what you want but it works

Please Login or Register  to view this content.

[soranz]

Thank you all! Looks like there are many ways to do it.
Appreciate the detailed explanations Theo and Leclerc

[xladept]

Hi Soranz,

Welcome to the Forum! In Excel it seems that there are always many paths to a solution - and thanks for the rep!