Separate TEXT

[DAA]

Is there a way to separate text.

A1= John A. Doe

I would like to separate the text on A1 that would show:

B1= John
C1= A (without the period)
D1= Doe

Help and thanks in advance.

 

[Frank Kabel]

Hi
one way:
Data - Text to columns
and use the space as delimiter

 

[Tod]

Assuming that the name will always have the same format

FirstName MiddleInitial. LastName

Formula Solution:

In B1 enter =LEFT(A1,FIND(" ",A1,1)-1)
In C1 enter =MID(A1,FIND(".",A1)-1,1)
In D1 enter =RIGHT(A1,LEN(B1)+LEN(C1)+2)

Copy these formulas down for as far as you have data in
column A.

tod

 

[DAA]

B1 and C1 works but D1 only shows "oe" (instead of Joe).
Thanks again.

 

[Ken Wright]

In this instance you will also need to tick Other and put a period in the box.
Make sure that 'Treat Consecutive Delimiters as one' is checked.

 

[Domenic]

Hi,

try,

B1=LEFT(A1,FIND(" ",A1)-1)
C1=MID(A1,FIND(" ",A1)+1,1)
D1=RIGHT(A1,LEN(A1)-(FIND(".",A1)+1))

Hope this helps!

 

[Dave R.]

You mean instead of Doe? Try that;

=RIGHT(A1,LEN(B1)+LEN(C1)+1)

 

[Dave R.]

Yipes, sorry.

=RIGHT(A1, LEN(A1)-SUMPRODUCT(LEN(B1:C1))-3)

 

[DAA]

Sorry it didn't work. It shows "n D. Doe"

-----Original Message-----
Yipes, sorry.

=RIGHT(A1, LEN(A1)-SUMPRODUCT(LEN(B1:C1))-3)







.

 

[DAA]

Thanks Domenic. It worked perfectly!

-----Original Message-----
Hi,

try,

B1=LEFT(A1,FIND(" ",A1)-1)
C1=MID(A1,FIND(" ",A1)+1,1)
D1=RIGHT(A1,LEN(A1)-(FIND(".",A1)+1))

Hope this helps!


.

 

[Dave R.]

I typed "John A. Doe" into A1, then used the formulas provided in the post
in B1, C1, and changed the D1 formula and it worked fine.

Good day.

 

[Domenic]

Thanks Domenic. It worked perfectly!

Glad I could help! Although, Text-to-Columns would probably be easier,
as was suggested earlier.