Excel Gurus: How can I separate data inside one column into two columns?

Question

Eg.. i have a massive long list of prices that I am trying to sort high to low except excel won't let me do it using A-Z sort.
Each row has the following inside each cell:
Column A
$1200 /pc
$850 /pair
$350 /set
How can I separate into two columns the price and the /pc etc into the next column so I can sort high to low:
Column A
$1200
$850
$350


Column B
/pc
/pair
/set


You will make someone somewhere very happy.

Thanks in Advance.

Answer 1

Yes it is possible, the explanation is a bit long.

For the explanation each part of the formula will be on one cell:

You will need to use these functions: LEN,FIND,LEFT, RIGHT, VALUE

Lets say that on cell A1 you have $1200/pc

You need to

• Obtain the amount of characters of the text string ==> On cell B1 write this formula =LEN(A1)

• Obtain the location of the "/" ==> On cell C1 write this formula =FIND("/",A1)



With the above values you can separate the information into 2 parts

EXTRACT THE FIRST PART

• On cell D1 write this formula =LEFT(A1,C1-1)

• The dollar amount is in TEXT format, if you need it for calculations, you have to convert it to a numeric value using the VALUE function ==> on cell E1 write this formula =VALUE(D1)

TIP: If you want to display the $ sign, then format the column



EXTRACT THE SECOND PART

• On F1 write this formula =RIGHT(A1,B1-C1+1)



To use one formula for each part:

• For the FIRST PART use this formula

=left(A1,find("/",A1)-1) or =value(left(A1,find("/",A1)-1))

• For the second part use this formula

=RIGHT(A1,LEN(A1)-FIND("/",A1)+1)



TIP: For explanation about the functions used check EXCEL help



Good Luck

Answer 2

1

Answer 3

The method that "Chi Yan" gave is the most accurate and fastest in my opinion. I would just change one thing. I don't know if Excel 2002/2003 has this option, but if you're using 2007, instead of clicking "fixed width" in the Text to Columns Wizard, click "Delimited" instead. That way when you click next it will ask you what character your data is separated by (semicolon, comma, etc). This way it won't matter how many numbers you have in front of the text. If you want to use the line method used in "Fixed With" all of your data has to be the same amount of numerical characters, which by your example doesn't seem to be the case.



Good Luck.

Answer 4

This feature is called "Convert Text to Columns". Here are the instructions for Office 2002/2003. You should be able to find instructions for other versions by experimenting or by searching for it.





Select the cell or range of cells, and then click Text to Columns on the Data menu.

In Step 1 of the Convert Text to Columns Wizard, click Fixed Width, and then click Next.

In the Data preview window, drag a line to indicate where you want the content to be divided.





Tip To delete a line, double-click it.



Click Next.

In Step 3, select a column in the Data preview window, and then click a format option under Column data format.

Repeat this step for each column in the Data preview window.



If you want to show the divided content in the columns next to the full name, click the icon at the right of the Destination box, and then click the cell next to the first name in the list.





Important If you do not specify a new destination for the new columns, the divided data will replace the combined data.



Click the icon at the right of the Convert Text to Columns Wizard.





Click Finish.

Answer 5

highlight the cells you would like to merge right click format cells under alignment place a tick mark on merge cells or if you have a small letter "a" with arrows left and right on your toolbars, just highlight the cells and click it. oops... warning! if you merge them. the data on the other cell will be deleted.

Answer 6

i think if you use the "text to colums" tab this will help, highlight the row and then click on "text to colums" change to FIXED WIDTH and this might help, just click next and then finish..