Text to Columns via formula?
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Text to Columns via formula?

  1. #1
    Board Regular
    Join Date
    Mar 2010
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Text to Columns via formula?

    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.

  2. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Text to Columns via formula?

    Try

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

    Filled right as far as needed.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  3. #3
    Board Regular
    Join Date
    Mar 2010
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Text to Columns via formula?

    You sir are a master!!
    Woks perfect!

  4. #4
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Text to Columns via formula?

    Glad to help, thanks for the feedback
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  5. #5
    New Member
    Join Date
    May 2013
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Text to Columns via formula?

    Quote Originally Posted by Jonmo1 View Post
    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.

  6. #6
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Text to Columns via formula?

    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)))
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  7. #7
    New Member
    Join Date
    May 2013
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Text to Columns via formula?

    Quote Originally Posted by Jonmo1 View Post
    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.

  8. #8
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Text to Columns via formula?

    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.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  9. #9
    New Member
    Join Date
    May 2013
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Text to Columns via formula?

    Quote Originally Posted by Jonmo1 View Post
    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......

  10. #10
    New Member
    Join Date
    Oct 2014
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Re: Text to Columns via formula?

    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 by achin1981; Oct 3rd, 2014 at 05:23 PM.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •