Formula to select the first name & second name

[MAHMUZ]

Hi;
I am looking to write a the correct formula in order to select the first name of each actor from column (B) and put it under the column (D) under First name, and again to select the second name from column (B) and put it under the column (E) as Second name....etc.

the attached file showing the name of actors, this is just only a sample of my file. However in column (B3) it showing the name of TOM HANKS therefore the first name will be under (D3 = TOM) and the second name will be under (E3=HANKS) and...etc, and what if i have more than 3 names like (first name, second name, Surname) what can be the formula condition?!!!

BR

[canapone]

Hi,

I do not think there is a chance to figure out a formula that the name is composed of 1/2/3 segments.

You can use this system: I hope practical.

In D3:

=LEFT(B3,FIND("@",SUBSTITUTE(B3," ","@",F3))-1)

In E3

=TRIM(SUBSTITUTE(B3;D3;""))

In F3

a helper column where you have to specify how many segments you need to extract as first names.

Hope it helps

[pike]

Hi MAHMUZ,

D3

Please Login or Register  to view this content.

E3

Please Login or Register  to view this content.

[DonkeyOte]

...what if i have more than 3 names like (first name, second name, Surname) what can be the formula condition?

That rather depends on

a) consistency of convention

b) whether you want all but surname returned to Col D or just forename

Assuming a consistent delimiter and that you want all but surname returned in First Name column then:

Please Login or Register  to view this content.

Obviously if you want just forename replace D3 with Pike's suggestion.

[MAHMUZ]

Thanks for every one participating in this case and i totally appreciate your effort whatsoever the result be.

However all the suggested formula were just solving a part of my request because ever each formula has its own criteria, in column D or E, there is not unify formula which can gather both concept and there is no chance to get the name of any actors in three column like

First Second Third
Tom Hanks Mathew Tom Hanks Mathew
Johnny Depp Colin Johnny Depp Colin

BR

[DonkeyOte]

The above makes little sense to me I'm afraid.

I would suggest you post a further sample file with expected results for a wide variety of names should they contain 0, 1 or more middle names.

Your earlier sample file was obviously of limited use given the nature of your question.

[MAHMUZ]

The above makes little sense to me I'm afraid.

I would suggest you post a further sample file with expected results for a wide variety of names should they contain 0, 1 or more middle names.

Your earlier sample file was obviously of limited use given the nature of your question.

Dear DonkeyOte: Sorry to reply you late .

The attached file will describe my request and also what the result should be looks like when you run the formula, either one formula covering the three columns, or one formula for each column.

BR

[DonkeyOte]

Based on your sample strings / expected results

Please Login or Register  to view this content.