Need to move part of a text string to another column in Excel

impala6 used Ask the Experts™
I have a spreadsheet with this value in A1.  This is the actual text in A1:  

     Active/Inactive                <-- Active/Inactive

What I need to do is move the "<-- Active/Inactive" to column B1 and leave "Active/Inactive" in column A1.

I have a list of 50 values and all of the values that need to move to column B are prefaced with this string: "<-- "
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
One way to do it is to use "Text to Columns"


1. Select your data
2. Go to "Data" tab
3. Select "Text to Columns"
4. Choose "Fixed width"
5. Click next
6. Move the Arrow where you want to split your data
7. Click Next
8. Choose destination cell to be pasted
9. Click finish.
Top Expert 2016


pls try
Sub macro()
For Each c In Range(Range("A1"), Range("A" & Cells.Rows.Count).End(xlUp))
    Pos = InStr(1, c, "<--")
    If Pos > 0 Then
        c.Offset(0, 1) = Trim(Mid(c, Pos))
        c.Value = Trim(WorksheetFunction.Clean(Left(c, Pos - 1)))
    End If
End Sub

Open in new window



Thanks for the quick response!  This worked perfectly!
Professor JMicrosoft Excel Expert
Top Expert 2014

try this macro

Sub Test()

    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(22, 1), Array(50, 1)), TrailingMinusNumbers _
   Dim cell As Range
Dim rng As Range

  If Selection.Cells.Count = 1 Then
    Set rng = Selection
    Set rng = Selection.SpecialCells(xlCellTypeConstants)
  End If

  For Each cell In rng.Cells
    cell.Value = Application.WorksheetFunction.Clean(Trim(cell.Value))
  Next cell
End Sub

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial