Separating data in columns

[ill618]

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I'm needing to separate some data in columns. Here is a sample:
Thomas, Nehemiah JR 1,837.05 ; Tiller, Tanika 2,163.72 ; Tobin, Terri 1,182.66 ;

That is all in one cell. The individual entries are separated by semicolons. I need to get the names in separate cells below each other in the same column, and the numbers next to them in the same row and not in the same cell with the names. Like this:

A B
Thomas, Nehemiah JR 1,837.05
Tiller, Tanika 2,163.72
Tobin, Terri 1,182.66

Is there a formula I could use, or a way to do that automatically? Any way at all to at least avoid separating the names manually and then separating the numbers manually and deleting them in the other cell?

That might be incredibly difficult, so here is a hopefully easier problem related to my first. This is an example of another cell which I need to separate data:
TRIUMPH LEARNING 2539

This data is in the same cell, I need to get it in two separate cells, and the number deleted from the first cell, like above. I know that I can use the RIGHT function to get the number in the cell next to it, but is there a way to then make it so that the number is deleted from the cell with the words?

 

[CyberTaz]

OK, let's take this one step, one issue at a time. The first concern is to
get the data that's al stuffed into a single cell into a column.

1- Select the cell, then go to Data> Text to Columns
2- Select the Delimited option, then click Next
3- Check the box for Semicolon, clear the check on any on the option, then
click Finish
4- Select the cells the data now appears in & Copy
5- In another area go to Edit> Paste Special
6- Click Transpose at the bottom of the dialog, then click OK


HTH |:>)
Bob Jones
[MVP] Office:Mac
As for separating the names from the figures it will depend to a certain
extent on whether each figure is the same number of characters. If it is,
the RIGHT() function you already seem familiar with will let you extract
them to another column.

I don't have a VBA solution for then separating the content into 2 columns
for either of your two lists, which is what you would need to delete content
from the cell. There is no function that deletes data. However, if someone
else doesn't come along with a more elegant solution I'll be glad to share
how I would go about it. In the mean while the above steps will give you the
columnar list you're looking for.