Copy/move values matching a pattern to another column

Question

I have a worksheet where we need to move all the values matching a pattern to different columns.

For example, we have a B column with values that are one or more letters followed by one or more digits, like k123, k4567, g345, g65535, HJ4567, KL4553, etc. I need to place all values starting with k in column C, all values starting with g in column D, and so on.

Is there any macro to do it?

Solution

This code will create a macro to move your data based on the letter preceding the number in each cell of column B. Paste this into the VBA Explorer (Alt+F11) and it will show up in the macro window as MoveData.

You will see the code looks at each cell to see what the left most character is. If its a k the cell contents are copied to the cell 1 place to the right (column C), then clears the original cell. If the character is a g, it is copied to the cell 2 places to the right (column D), then clears the original cell.

Sub MoveData()
Dim myrange, cell As Range
Set myrange = ActiveSheet.Range("B:B", Range("B:B").End(xlDown))
For Each cell In myrange
    If Left(cell.Value, 1) = "k" Then
        cell.Offset(0, 1).Value = cell.Value
        cell.ClearContents
    ElseIf Left(cell.Value, 1) = "g" Then
        cell.Offset(0, 2).Value = cell.Value
        cell.ClearContents
    End If
Next cell

    'Delete empty cells in column C
    Columns("C:C").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete Shift:=xlUp

    'Delete empty cells in column D
    Columns("D:D").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("A1").Select
End Sub

You can change the values it looks for by modifying the "k" or "g" in the IF statements.

Disclaimer: Use at your own risk. Make a backup copy of your work. Not responsible for any lost data.

Comments

[CharlieRB]

What have you tried to make this happen? Do you just want the values to show or are you wanting to move them (cut/paste)? Does it have to be a macro?

[william]

i want to move them to new columns ansd it has to be a macro because i created a template with button in a cell and when the user presses the button it should automatically to the rest

[william]

I have a worksheet template where i created a button and assigned couple of macros to it one is splitandtranspose and another one is duplicate values so when i run the button it will just split the data in a column to mulitple rows and then highlight the cell containing duplicates now i need to move all the similar formats starting with Hi or Gi or HJ to a different column so i thought of a macro and if i assign this macro to existing button then it will automatically do the rest thats my thought. I can do it manually but it takes lot of time to do it there are likes 1000's of rows

[CharlieRB]

It only moves data across a row and only the data which starts with either k or g, as outlined by your question. If there are values starting with anything else it will leave it in column B. That and blank cells in column B with result in gaps in column C and D.

[CharlieRB]

Updated the code to include the empty cells being removed.