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.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

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

ADVERTISEMENT

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

ADVERTISEMENT

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:

Forum statistics

Threads
1,148,272
Messages
5,745,790
Members
423,973
Latest member
man_this_is_hard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top