Take value after comma, cut and paste in another column

cgmojoco

Well-known Member
Joined
Jan 15, 2005
Messages
696
Hi there-

Need a macro that will search each cell in column J
Find value after the ","
Place the value in cell column H on the same line


Example of cell in column J
HENDERN, 12

I need to extract the 12 and put it in the H column on the same row

Also need a check run to make sure each value in column J has a comma in it, if not, error.
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
Code:
Sub test()
Dim r As Range, x, ff As String
With ActiveSheet
    Set r = .Columns("j").Find(",", , , xlPart)
    If Not r Is Nothing Then
        ff = r.Address
        Do
            x = Trim(Mid(r, InStr(r, ",") + 1))
            If IsNumeric(x) Then x = CDbl(x)
            r.Offset(, 1) = x
            Set r = .Columns("j").FindNext(r)
        Loop Until r.Address = ff
    End If
End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,013
Messages
5,569,620
Members
412,283
Latest member
camgree
Top