Removing first character and last 3 in range

[figuare9]

I'm a bit stumped trying to remove some characters from a string with VBA.

I can sort of get it working with a formula using =Left... but I can only get one side working, but I'd prefer to use a command button to set it up, so I'd like to accomplish it in vba.


In range AJ1:AJ200 I have a several different wages.

E.g.
[TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD]$80.70/hr[/TD]
[/TR]
[TR]
[TD]$69.70/hr[/TD]
[/TR]
[TR]
[TD]$60.525/hr[/TD]
[/TR]
[TR]
[TD]$60.22/hr[/TD]
[/TR]
[TR]
[TD]$53.12/hr[/TD]
[/TR]
[TR]
[TD]$52.275/hr[/TD]
[/TR]
[TR]
[TD]$48.30/hr[/TD]
[/TR]
[TR]
[TD]$40.35/hr[/TD]
[/TR]
[TR]
[TD]$34.85/hr[/TD]
[/TR]
[TR]
[TD]$24.15/hr[/TD]
[/TR]
[TR]
[TD]$17.25/hr[/TD]
[/TR]
[TR]
[TD]$15.25/hr[/TD]
[/TR]
</tbody>[/TABLE]


Some have 3 decimal points, some have 2. However, all of them have "/hr" at the end, and "$" in the beginning.

I'd like to remove all "$" & "/hr" from the entire range in VBA and convert these to a number.

 

[kweaver]

You could use a formula: =0+LEFT(RIGHT(AJ1,LEN(AJ1)-1),LEN(RIGHT(AJ1,LEN(AJ1)-4))) and fill down
Or VBA per this formula:

Sub remove()
Dim LR As Long, i As Integer
LR = Cells(Rows.Count, "AJ").End(xlUp).Row
For i = 1 To LR
  Cells(i, 36) = 0 + Left(Right(Range("AJ" & i), Len(Range("AJ" & i)) - 1), Len(Right(Range("AJ" & i), Len(Range("AJ" & i)) - 4)))
Next
End Sub

 

[figuare9]

You could use a formula: =0+LEFT(RIGHT(AJ1,LEN(AJ1)-1),LEN(RIGHT(AJ1,LEN(AJ1)-4))) and fill down
Or VBA per this formula:

Sub remove()
Dim LR As Long, i As Integer
LR = Cells(Rows.Count, "AJ").End(xlUp).Row
For i = 1 To LR
  Cells(i, 36) = 0 + Left(Right(Range("AJ" & i), Len(Range("AJ" & i)) - 1), Len(Right(Range("AJ" & i), Len(Range("AJ" & i)) - 4)))
Next
End Sub

That was quick! This forum never disappoints... I love it here!

Thanks Kweaver!

 

[Peter_SSs]

Instead of doing it one row at a time, what about processing the whole column at once (twice)?

Sub Remove_Characters()
  With Range("AJ1", Range("AJ" & Rows.Count).End(xlUp))
    .Replace What:="$", Replacement:="", LookAt:=xlPart
    .Replace What:="/*", Replacement:="", LookAt:=xlPart
  End With
End Sub

If you did want a formula approach, here is a simpler one:

=MID(AJ1,2,LEN(AJ1)-4)+0

 

[figuare9]

Instead of doing it one row at a time, what about processing the whole column at once (twice)?

Sub Remove_Characters()
  With Range("AJ1", Range("AJ" & Rows.Count).End(xlUp))
    .Replace What:="$", Replacement:="", LookAt:=xlPart
    .Replace What:="/*", Replacement:="", LookAt:=xlPart
  End With
End Sub

If you did want a formula approach, here is a simpler one:

=MID(AJ1,2,LEN(AJ1)-4)+0

That's great! Thank you. That might actually come in handy! I appreciate it

 

[Peter_SSs]

That's great! Thank you. That might actually come in handy! I appreciate it

You're welcome.

The previous code is probably easier to follow, but you could use the formula idea in code to do it with one less code line (& this time only processing the column once).

Sub Remove_Characters_v2()
  With Range("AJ1", Range("AJ" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(#="""","""",mid(#,2,len(#)-4))", "#", .Address))
  End With
End Sub