microsoft excel 2007 - Separate cell content after "-" for multiple columns at once

 -------------------------------------------- 
|  Visible |   Actual formula   |  I need    |
 --------------------------------------------
|    0     |   =500-500         |  500       |
|    0     |   =10000-10000     |  10000     |
|    0     |   =1500-1500       |  1500      |
|    0     |   =750-750         |  750       |
|          |                    |            |
 -------------------------------------------- 

I can do above separation by using Text To Column from Data tab in MS Office Excel 2007. But I have many columns which couldn't select and apply Text to column at once. Is it possible with any other easy way?

Answer

So you want to apply this method to multiple columns at once, right?
I don't see how this could be done without VBA, so here is my solution:

Sub text2columns()

separator = "-"
FirstCol = ActiveSheet.Cells.Find("*", [A1], , , xlByColumns, xlNext).Column
LastCol = ActiveSheet.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column

For i = LastCol To FirstCol Step -1

    Set test = ActiveSheet.Columns(i).Find(separator, , xlFormulas, xlPart)

    If Not test Is Nothing Then
        Set FirstCell = ActiveSheet.Columns(i).Find("*", , , , , xlNext)
        Set LastCell = ActiveSheet.Columns(i).Find("*", , , , , xlPrevious)

        Columns(i + 1).Insert Shift:=xlToRight
        Application.DisplayAlerts = False
        Range(FirstCell, LastCell).TextToColumns Destination:=FirstCell, _
            DataType:=xlDelimited, Other:=True, OtherChar:=separator
        Application.DisplayAlerts = True
    End If

Next
End Sub

Quick overview

1. Iterate backwards through each column


2. Test if seperator string - is present in current column


3. If yes, insert an new column right next of it


4. And apply text-to-column method to current column

^{This macro will fail if you have more than one separator in any cell}

Before

After

Sample worksheet