Loop through collumn

[Sebas123]

Hello,

I have a excel sheet that houses some wrong data in the first collumn. The lay-out is as following.

Column A:
1111-111111.ipt,
1111-111112.ipt,
1111-111113.ipt ect.

The amount of rows in this column variable. (From 5 to 50).

I want to loop through the collumn and remove the ".ipt" in every cell. Can somebody help me with this?


Thanks,
Sebas123

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        .Value = Replace(.Value, ".ipt", "")
    End With
Next i
End Sub

 

[Sebas123]

Never mind...

Gonna try it now.

 

[machopicho]

=REPLACE(A1,FIND(".",A1),MID(A1),"")
Fill down

 

[Sebas123]

Try

Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        .Value = Replace(.Value, ".ipt", "")
    End With
Next i
End Sub

Thank you so much,

This is perfect.

Thanks,
Sebas123

 

[Rick Rothstein]

I'm pretty sure this one-liner macro will do what you want (and it should be quite snappy about it)...

Sub DeleteDotIPT()
  Columns("A").Replace ".ipt", "", xlPart
End Sub

You could, of course, do the same thing without using a macro. Select Column A, press CTRL+H and put .ipt (note the leading dot) in the "Find what" field, leave the "Replace with" field empty and click "Options>>" to see all the options and make sure "Match entire cell contents" is not checked, then click the "Replace All" button.

 

[Comfy]

USe the built in Find and Replace function?

Find What: .ipt
Replace With: Leave this blank