# Thread: Split a cell (2000)

1. ## 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. ## Re: Split a cell (2000)

The English is Delimited.

3. ## Re: Split a cell (2000)

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

4. ## 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

5. ## 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. ## 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. ## 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.