Excel Vba to get text from LEFT of column

[matthewlrx]

Hi all

I have data which gets sent to me but this data has 2 parts of data in each cell. I only need the left (or first) part of each cell.

In the picture below you will see the first column is time, I only need 11:01min and the second and third column il only need the first percentage number. Is there a VBA i can run that will go change each cell in each column? The tricky part is the percentage can change so it could be 0% or 88% so i cant choose the normal LEFT,3 option.

 

[Z51]

For just A1:C1, you could use:

Sub matthewlrx()
Range("A1").Value = Left(Range("A1").Value, InStr(1, Range("A1").Value, "min") + 2)
Range("B1").Value = Left(Range("B1").Value, InStr(1, Range("B1").Value, "%"))
Range("C1").Value = Left(Range("C1").Value, InStr(1, Range("C1").Value, "%"))
End Sub

This could be adjusted to loop through multiple cells in each column, rather than just looking at those three cells, of course.

 

[matthewlrx]

For just A1:C1, you could use:

Sub matthewlrx()
Range("A1").Value = Left(Range("A1").Value, InStr(1, Range("A1").Value, "min") + 2)
Range("B1").Value = Left(Range("B1").Value, InStr(1, Range("B1").Value, "%"))
Range("C1").Value = Left(Range("C1").Value, InStr(1, Range("C1").Value, "%"))
End Sub

This could be adjusted to loop through multiple cells in each column, rather than just looking at those three cells, of course.

that works great thank you. How would i loop it from the second line all the way down to the last row though?

 

[Z51]

Try this. It assumes your last row is the same in all columns, so finds the last used row in column A. If the columns end at different rows, you'd just need to find that value and apply it to the ranges.

Sub matthewlrx2()
Dim lastrow As Long, ce As Range
lastrow = Range("A" & Rows.Count).End(xlUp).Row

For Each ce In Range("A1:A" & lastrow)
    ce.Value = Left(ce.Value, InStr(1, ce.Value, "min") + 2)
Next ce

For Each ce In Range("B1:C" & lastrow)
    ce.Value = Left(ce.Value, InStr(1, ce.Value, "%"))
Next ce

End Sub

 

[matthewlrx]

Try this. It assumes your last row is the same in all columns, so finds the last used row in column A. If the columns end at different rows, you'd just need to find that value and apply it to the ranges.

Sub matthewlrx2()
Dim lastrow As Long, ce As Range
lastrow = Range("A" & Rows.Count).End(xlUp).Row

For Each ce In Range("A1:A" & lastrow)
    ce.Value = Left(ce.Value, InStr(1, ce.Value, "min") + 2)
Next ce

For Each ce In Range("B1:C" & lastrow)
    ce.Value = Left(ce.Value, InStr(1, ce.Value, "%"))
Next ce

End Sub

Fantastic, thank you so so much this works GREAT.