0
 -------------------------------------------- 
|  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?

1 Answer 1

0

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

enter image description here

After

enter image description here

Sample worksheet

1
  • Sorry dude, I didn't chk your answer for a long period. Apr 14, 2015 at 17:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.