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

impala6
impala6 used Ask the Experts™
on
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: "<-- "
Comment
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"

Steps:

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

Commented:
Hi,

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
Next
End Sub

Open in new window

Regards

Author

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

Commented:
try this macro

Sub Test()
'

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


  If Selection.Cells.Count = 1 Then
    Set rng = Selection
  Else
    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