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.

 

[Jonmo1]

Try

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),((COLUMNS($A1:A1)-1)*LEN($A1))+1,LEN($A1)))

Filled right as far as needed.

 

[TWTHOMAS]

You sir are a master!!
Woks perfect!

 

[Jonmo1]

Glad to help, thanks for the feedback

 

[deepfunk9]

Try

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),((COLUMNS($A1:A1)-1)*LEN($A1))+1,LEN($A1)))

Filled right as far as needed.

This is great. Thank you. I can't quite work out how to get the second word after the space.
For example, Smith John Thomas
would give the result "John".

I've tried lots of modifications of the original but can't seem to work it out.

 

[Jonmo1]

Welcome to the board.

To get ONLY the 2nd word in the string, try
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1),LEN(A1)))

 

[deepfunk9]

Welcome to the board.

To get ONLY the 2nd word in the string, try
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1),LEN(A1)))

You Sir, are a genius. Thank you.

Are you able to let me know what the columns part and the -1 and +1 parts were for in the initial formula? I couldn't quite work out that bit.
I would have thought that COLUMNS($A1:A1) would always give a value of "1".
And why are you adding all the spaces in there? I have spent some time trying to work out this formula and I don't quite get it.

Thanks for your help.

 

[Jonmo1]

Actually, notice the $ in the columns function.
=COLUMNS($A1:A1)

go ahead and put that by itself into a cell, and drag it to the right to see what happens.

 

[deepfunk9]

Actually, notice the $ in the columns function.
=COLUMNS($A1:A1)

go ahead and put that by itself into a cell, and drag it to the right to see what happens.

Doh! That was really easy. I've spent hours trying to work it out and all I needed to do was drag the column across......

 

[achin1981]

Hi
I work for travel company, I copy the itinerary from the Reservation system. While sending the quote, I need to make sure the format looks presentable.
The format appears like this:

1 EY 7603 M 06NOV DELAUH HK1 2055 2325
OPERATED BY JET AIRWAYS (INDIA) LTD
2 EY 11 M 07NOV AUHLHR HK1 0235 0635
3 EY 18 V 18NOV LHRAUH HK1 2020 #0720
4 EY 7602 V 19NOV AUHDEL HK1 1040 1540
OPERATED BY JET AIRWAYS (INDIA) LTD


I want the above data to appear in way, where each item appears separately in column. I do not want to use text to column function in the ribbon
Also the letters marked in bold are not required and note highlighted in Italics should appear against the preceding row in separate column

The result should appear as :


1 EY 7603 06NOV DEL AUH 2055 2325 OPERATED BY JET AIRWAYS (INDIA) LTD
2 EY 11 07NOV AUH LHR 0235 0635
3 EY 18 18NOV LHR AUH 2020 #0720
4 EY 7602 19NOV AUH DEL 1040 1540 OPERATED BY JET AIRWAYS (INDIA) LTD

Kindly please advise

Achin