Text to Columns via formula?

TWTHOMAS

Board Regular
Joined
Mar 26, 2010
Messages
90
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.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try

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

Filled right as far as needed.
 

deepfunk9

New Member
Joined
May 10, 2013
Messages
3
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

New Member
Joined
May 10, 2013
Messages
3
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

New Member
Joined
May 10, 2013
Messages
3
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

New Member
Joined
Oct 3, 2014
Messages
1
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,095,785
Messages
5,446,486
Members
405,403
Latest member
horace james

This Week's Hot Topics

Top