vba Remove last 5 characters from string in table column

[brentcook]

Have a table in a sheet that is connected with info on sharepoint. Wondering if there is a way to remove the last 5 characters (.xslx, .docx, .xlsm etc.) from an entire column. Any ideas?

 

[jolivanes]

For The 5th Column (Column E).

Sub Maybe()
Dim c As Range
With Range("E1")
    For Each c In Range("E2", .Offset(.CurrentRegion.Rows.Count - 1))
        c.Value = Left(c.Value, Len(c.Value)-5)
    Next c
End With
End Sub

 

[DanteAmor]

Try this
Assuming your data starts in cell A1

Sub right5()
  With Range([COLOR=#ff0000]"A1[/COLOR]", Range("[COLOR=#ff0000]A[/COLOR]" & Rows.Count).End(xlUp))
    .Value = Evaluate("=IF({1},REPLACE(" & .Address & ",LEN(" & .Address & ")-4,5,""""))")
  End With
End Sub

Or this

Sub right5()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate("=IF({1},MID(" & .Address & ",1,LEN(" & .Address & ")-5))")
  End With
End Sub

 

[brentcook]

Thank you both for getting back to me! My question was full of holes. Sorry for the misunderstanding. I have a Pivot Table in column I labeled "DischargedPatients" that I need to remove the last five characters from the string in each row of the table. And I need it to occur after a refreshed connection upon opening the workbook. Thank you again for your help.

 

[brentcook]

Again, thanks for the reply. Had it working perfectly, but when I run it from a command button from another sheet, it returns #Value for the entire column. Not sure what is going on.

Here's what I have:

Sub New_Patient()

   
    With Sheet1.Range("c1", Sheet1.Range("c" & Rows.Count).End(xlUp))
        .Value = Evaluate("=IF({1},REPLACE(" & .Address & ",LEN(" & .Address & ")-4,5,""""))")
  
    End With


   
    UserForm3.Show
    
End Sub

Any ideas?

 

[Mentor82]

Hi,
How about this approach?

Sub New_Patient()
Dim rng as range
Dim sh as worksheet
Dim rCell as range
Set sh=worksheets(1)
Set rng=ws.Range("c1", ws.Range("c" & Rows.Count).End(xlUp))

For each rCell in rng
 
If rCell.Value<>"" then 
 rCell.value=left(cstr(rCell.value),len(cstr(rCell.value))-5)
End if
Next

Set rng = nothing
Set sh = nothing
    UserForm3.Show
    
End Sub

 

[DanteAmor]

It has to be on the sheet where the data is.
Try this:

Sub New_Patient()
    [COLOR=#0000ff]Sheet1.select[/COLOR]
    With Range("c1", Range("c" & Rows.Count).End(xlUp))
        .Value = Evaluate("=IF({1},REPLACE(" & .Address & ",LEN(" & .Address & ")-4,5,""""))")
    End With
    UserForm3.Show    
End Sub

 

[brentcook]

Works perfectly! Such a simple fix. Thanks again.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[mse330]

Sub right5()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate("=IF({1},MID(" & .Address & ",1,LEN(" & .Address & ")-5))")
  End With
End Sub

Hi Dante … Just trying to understand your Evaluate line, what is the purpose of IF({1} & what does it mean ? I have tried to produce the same line without it as shown below & it works just fine with my small sample

Sub test()
With Sheet1.Range("B1", Sheet1.Range("B" & Rows.Count).End(xlUp))
    .Value = Evaluate("=mid(" & .Address & ",1,len(" & .Address & ")-5)")
End With
End Sub