Fresh Excel Tips

In this exercise, we have values in cells that need to be divided and put into separate cells. For example, in cell Cell A1, we have Mail-man and we need to put both Mail and man into separate cells separated by “-”. To do this, we will use the TRIM, LEFT, MID and FIND Functions. Sounds like a lot of work but I would rank the difficulty of this a 5/10. Anyhow, here is how we’ll divide the values. This will work in Excel 97, 2000, 2003 and 2007.

1. Enter the values that you want to be divided in cells A1:A10
2. To get the first half of the value, enter this formula into B1: =TRIM(LEFT(A1,FIND(”-”,A1)-1))
3. To get the second half, enter this formula into C1: =TRIM(MID(A1,FIND(”-”,A1)+1,9999))

What this does is look for the “-” character and trims the data accordingly. You should now be set!

Also, if you want to separate a certain amount of characters, for example the first 3 digits from a ten digit number, then you can use the formula below: =LEFT(A1,3)

To return the last 7 values from the cell, use this formula: =RIGHT(A1,7). You can modify this for your own application.

1. Separate First name from Last name using SEARCH in Excel
2. How to Use the TRIM function to delete spaces in Excel
3. How to Extract digits from a cell using the MID Function in Excel
4. Using the SUBSTITUTE function to substitute parts of a cell
5. How to use the TEXT function to extract parts of a date in Excel