separate Name into two columns

[JohnSeito]

Hello all,

I am wondering if anyone know if there is a formula that can separate a name which sometimes have two words or one or two space. For example a formula that can separate a name by first space in one column and the second half in the second column.

example : David T Blain

name is in column A,

column B is David T
column C is Blain.

I don't think this is possible with a formula, but if you think it is let me know.

I think it would be more efficient to do this with a macro, could you please see if you could provide a macro that can do this.

Thanks

[rylo]

Hi

If your data is in A1 then try:
B1:

Please Login or Register  to view this content.

C1:

Please Login or Register  to view this content.

HTH

rylo

[JohnSeito]

Ok thanks. Do you know if these formula works in visual basics? I would like to have the option of using it either as formula or formula in visual basics. thanks.

[rylo]

Hi

You could either used EVALUATE on the formulas, or something like

Please Login or Register  to view this content.

rylo

[JohnSeito]

I am not sure what you mean by EVALUATE on the formula.

[mikerickson]

If "David T Blain" goes to "David T" - "Blaine"
, does "Leonardo da Vinci" go to "Leonardo da" - "Vinci"

Parsing FullName to First/Last by spaces is un-reliable. These functions might help, they default to splitting at the last space, but which space to split at can be specified for unusual names.

Please Login or Register  to view this content.

[Leith Ross]

Hello JohnSeito,

This macro is a UDF (User Defined Formula). You can use this like a worksheet formula. Place the UDF macro code in a standard VBA module. You can then use it on the worksheet like a formula. See the examples.

Examples

Please Login or Register  to view this content.

UDF Macro Code

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[rylo]

Hi

EVALUATE is a VBA function that will take a formula string, evaluate it, and provide the result of the evaluation. So something like

Please Login or Register  to view this content.

would put the result of the sum of A1:A3 into the variable xxx. You could do the same sort of thing with the functions used in Excel to provide the result.


rylo

[JohnSeito]

Thank you. I will take a look at your codes and try them out. If I have any questions I will let you know.

[JohnSeito]

Solved, thanks. I have checked it.

[bhagyesh2000]

Simple way..

select the column which you want to split in to two parts..

Go to DATA option..

Select TEXT TO COLUMNS..

Choose DELIMINATED..

TAB SPACE ( choose the option by which name is separated)

Go to NEXT.. and Finish..

But before doing this.. add the same number of columns in against the present column in which name is present.
( example : If name have three parts. Initial, first name and last name.. so add 2 columns after the column in which full name is present.