Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Nov 2002
    Location
    Camp Lejeune, North Carolina, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Split a cell (2000)

    Is it possible to take a cell (say with something like 5 apples typed in it) and split the data into two seperate cells to show 5 in one and apples in another?

    Semper Fi

    Clinton

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split a cell (2000)

    The English is Delimited.
    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Nov 2002
    Location
    Camp Lejeune, North Carolina, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split a cell (2000)

    there is the left, and mid formula, is there a right?

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Split a cell (2000)

    Yes, there is a right, so if A1 has 13 apples in it, then
    <pre>=LEFT(A1,SEARCH(" ",A1))</pre>

    gives 13, and
    <pre>=RIGHT(A1,LEN(A1)-SEARCH(" ",A1))</pre>

    gives apples. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Split a cell (2000)

    Thanks Legare, I'll add it to my list of translations. I have Jan Karel's file with translations of menu options, and also learned from him how to translate a worksheet formula into English, but captions in dialog boxes are more difficult.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Split a cell (2000)

    <P ID="edit" class=small>(Edited by HansV on 14-Nov-02 18:38. I corrected Separated to Delimited)</P>If you have a column of such cells that you want to split in a one-time operation:
    - Select the cells
    - Select Data/Text to Columns...
    - In the first step, select Delimited
    - In the second step, check Space
    - Click Finish.

    If you want to use formulas: say the text "5 apples" is in cell A1.
    =LEFT(A1,FIND(" ",A1)-1) returns the part to the left of the space.
    =MID(A1,FIND(" ",A1)+1,1000) returns the part to the right of the space (1000 is an arbitrary number larger than the length of the longest string you expect).

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split a cell (2000)

    Thank you all for your speedy reply on this. DesertWarrior is one of my students, and I "push" your site as a resource for much needed questions. He has quickly found this site to be "most valuable". Again...thank you for all you guys do...My personal choice here is the text to column answer, so very easy to do, but appreciate the left, right, and mid examples. I have marked them so that I will not forget how to set them up next time.
    NMPadgett
    PS To DesertWarrior..."you da 'man" [img]/forums/images/smilies/smile.gif[/img] Keep your bookmark here for all your questions
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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