Is there a Way - Other than Text to Columns

T

TrueParadox

Board Regular

Joined

Sep 29, 2004

Messages

58

• Oct 12, 2004

• #2

Ya you can do it with a set of find/mid but is it really worth all the trouble ???

 

Upvote 0

A

acw

MrExcel MVP

Joined

Feb 13, 2004

Messages

4,814

• Oct 12, 2004

• #3

Hi

have a look at the thread
http://www.mrexcel.com/board2/viewtopic.php?t=109868

In this there are a couple of methods for extracting the individual parts of a string separated by a space.


Tony

 

Upvote 0

T

TrueParadox

Board Regular

Joined

Sep 29, 2004

Messages

58

• Oct 12, 2004

• #4

B2 = MID(A2,1,FIND(" ",A2)-1)
C2 = MID(A2,LEN(B2)+2,FIND(" ",A2,LEN(B2)+2) - (LEN(B2)+2))
D2 =MID(A2,LEN(B2)+LEN(C2)+3,FIND(" ",A2,LEN(B2)+LEN(C2)+3)-(LEN(B2)+LEN(C2)+3))
E2 = =MID(A2,LEN(B2)+LEN(C2)+LEN(D2)+4,LEN(A2)-(LEN(B2)+LEN(C2)+LEN(D2)+3))

 

Upvote 0

jim may

Well-known Member

Joined

Jul 4, 2004

Messages

7,486

• Oct 12, 2004

• #5

TrueParadox

U R d'MAN;

Thanks,

 

Upvote 0

jim may

Well-known Member

Joined

Jul 4, 2004

Messages

7,486

• Oct 12, 2004

• #6

One Last Q:
Using an If() statement...

What if A2 has Mr. James May (Only)

Can I have a "" in D2 for the absence of a Middle Initial
and the E2 Show May ?

Thanks in advance......

ray:
ray:

 

Upvote 0

jim may

Well-known Member

Joined

Jul 4, 2004

Messages

7,486

• Oct 13, 2004

• #7

OK, I think I got it;
Formulas take care of either 3 or 4 part name components. Hooray



[/img]file:///C:/MSO2002/Office10/Addins/temp.htm#javascript:void(0);

 

Upvote 0

jim may

Well-known Member

Joined

Jul 4, 2004

Messages

7,486

• Oct 13, 2004

• #8

file:///C:/MSO2002/Office10/Addins/temp.htm#javascript:void(0);

 

Upvote 0

jim may

Well-known Member

Joined

Jul 4, 2004

Messages

7,486

• Oct 13, 2004

• #9

OK, I give up;;; How do I send the file to your screen?

 

Upvote 0

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Oct 13, 2004

• #10

Can I ask why you don't want to use Text to Columns?

 

Upvote 0