formula help to seperate names

[ganeshpoojary05]

hi everyone,

i need a formula to seperate names.

for example:
i have a name in range("A1") as "Alex Roy Harris". I want a formula to split the names. i want my output as B1 should be "Alex", C1 should be "Roy" and D1 should be "Harris". can anyone help me with the formula. Thanks...

 

[VoG]

Try this

Excel Workbook
	A	B	C	D
1	Alex Roy Harris	Alex	Roy	Harris
Sheet2

 

[ganeshpoojary05]

thanks a lot!!!

 

[ganeshpoojary05]

similarly is there any formula to seperate 4 names from a cell.

 

[VoG]

The easiest way is to select the cell(s) in column A, Data > Text to Columns, tick Delimited and specify Space as the delimiter.

 

[ganeshpoojary05]

yes, i know but i need this in a formula.

 

[VoG]

Try

Excel Workbook
	A	B	C	D	E
1	Alex Roy Fred Harris	Alex	Roy	Fred	Harris
Sheet2

 

[ganeshpoojary05]

Thanks a lot again...

 

[Maheshp]

Sir VOG

what is the use of "~" this symbol in formula ?

 

[VoG]

It could be any symbol that is unlikely to occur in the text string - you could use ! £ ^ and so on, or even CHAR(7)