Text to Columns via formula?

[TWTHOMAS]

Greetings,

I searched the forums and found several answers to this but have not found one that works the way I need it to.

I have a list of names that I import almost weekly.

I have to break these names into columns but really don't want to have to use text to coumns each time. This is because in reality I don't want to past the data into my primary work area since text to coumns tends to expand to make room.

What I would like to do is paste my list into Sheet two for example and then have formulas in sheet 1 that would extract the names.

The issue I am running into is that the names are variable.
For example I might have:

Smith John Thomas
Jones Thomas Howell III
Thompson Sue
Padget Eugene Rex Michael

These are always broken by spaces without exception.

I would like to find a way to place formulas in sheet one that would exmain column A in sheet two and split the names into however many columns are needed. The most I have ever found is six.

Does this make sense? If so, does anyone know a way?


Thanks in advance as usual.

 

[V620898]

1) TRIM(IFERROR(LEFT(LEFT(A4,50),FIND(CHAR(1),SUBSTITUTE(LEFT(A4,50)," ",CHAR(1),LEN(LEFT(A4,50))-LEN(SUBSTITUTE(LEFT(A4,50)," ",""))-1))-1),""))
2) TRIM(IFERROR(LEFT(MID(A4,LEN(B4)+2,51),FIND(CHAR(1),SUBSTITUTE(MID(A4,LEN(B4)+2,51)," ",CHAR(1),LEN(MID(A4,LEN(B4)+2,51))-LEN(SUBSTITUTE(MID(A4,LEN(B4)+2,51)," ",""))-2))-1),MID(A4,LEN(B4)+1,100)))
3) TRIM(IFERROR(MID(A4,LEN(B4)+LEN(C4)+3,100),""))

for three parts of address with len less than 50