+ Reply to Thread
Results 1 to 7 of 7

Text to columns, split at first space only

  1. #1
    Wowbagger
    Guest

    Text to columns, split at first space only

    I have a 2500 line column of street addresses along the lines of (123 E
    Grand Maple). How can I split this column into two columns, first column
    only the numeric portion, second column only the street portion even though
    the street portion may contain spaces?

    For simplicity's sake I can assume that the first space in the address
    represents where the split should occur and that any subsequent spaces
    should be ignored.



  2. #2
    Trevor Shuttleworth
    Guest

    Re: Text to columns, split at first space only

    Assuming the addresses start in cell A2

    =LEFT(A2,FIND(" ",A2)-1)

    =RIGHT(A2,LEN(A2)-FIND(" ",A2))

    And drag down ...

    Regards

    Trevor


    "Wowbagger" <none> wrote in message
    news:%23uG1tZTSGHA.1728@TK2MSFTNGP11.phx.gbl...
    >I have a 2500 line column of street addresses along the lines of (123 E
    >Grand Maple). How can I split this column into two columns, first column
    >only the numeric portion, second column only the street portion even though
    >the street portion may contain spaces?
    >
    > For simplicity's sake I can assume that the first space in the address
    > represents where the split should occur and that any subsequent spaces
    > should be ignored.
    >




  3. #3
    Forum Contributor
    Join Date
    04-21-2006
    Location
    Australia
    MS-Off Ver
    Office 2016
    Posts
    117

    Similar problem

    I have a similar problem however I have data that has 2 spaces and I need the numeral at the end. With the formula above I get the word and numeral.

    ie
    cell = word word: number

    need result
    number

    I had tried RIGHT(B143,FIND(" ",B143)-9) however the number is not always the same number of digits and it sometimes gives a result including part of the text before it, or not all of the number.

  4. #4
    Trevor Shuttleworth
    Guest

    Re: Text to columns, split at first space only

    Try:

    =VALUE(RIGHT(B143,LEN(B143)-1-FIND(" ",B143)))

    Regards

    Trevor


    "Aussie_Striker"
    <Aussie_Striker.26m8iy_1145625304.4886@excelforum-nospam.com> wrote in
    message news:Aussie_Striker.26m8iy_1145625304.4886@excelforum-nospam.com...
    >
    > I have a similar problem however I have data that has 2 spaces and I
    > need the numeral at the end. With the formula above I get the word and
    > numeral.
    >
    > ie
    > cell = word word: number
    >
    > need result
    > number
    >
    > I had tried RIGHT(B143,FIND(" ",B143)-9) however the number is not
    > always the same number of digits and it sometimes gives a result
    > including part of the text before it, or not all of the number.
    >
    >
    > --
    > Aussie_Striker
    > ------------------------------------------------------------------------
    > Aussie_Striker's Profile:
    > http://www.excelforum.com/member.php...o&userid=33710
    > View this thread: http://www.excelforum.com/showthread...hreadid=523252
    >




  5. #5
    Registered User
    Join Date
    06-18-2012
    Location
    New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Text to columns, split at first space only

    I have a similar problem. I have a column of information where some cells start with a space and others do not. I want to split the cells with a space at the start in to the adjacent cell and leave the cells that do not start with a space where they are.

    E.g.
    The spreadsheet documents search queries in an image database. I have indicated in red the cells i wish to move to the adjacent right cell.

    Capture.PNG

  6. #6
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,147

    Re: Text to columns, split at first space only

    Darcyclay,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  7. #7
    Forum Contributor Russell Dawson's Avatar
    Join Date
    03-01-2012
    Location
    Yorkshire, England
    MS-Off Ver
    2007
    Posts
    604

    Re: Text to columns, split at first space only

    Definitely worth starting a new thread. This is six years old!
    If I helped, please don't forget to add to my reputation. (click on the star below the post)

    If the problem is solved, please: Select Thread Tools (on top of your 1st post) -> Mark this thread as Solved.

    Failure is not falling down but refusing to get up.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1