Find and Replace question

[Z0827]

I have a LOT of data that I pulled a report on. It is in columns. One column has notes inside of it and previously I ran a query with access to pull the numbers out into the next column. Some of these numbers didn't pull because they include 1-4 or 2-3 type numbers. Is there a way to find the cells that have numbers like that and replace them into the next cell with a different number?

For instance if in cell R493 is says 2-3, I want to put 2.5 into cell S493. I could do it manually, but there will be a total of around 20 thousand lines to look at. Any easy way to do this?

Thanks!

 

[Z0827]

Ahh, yes that is much more complicated. The problem is going to be determining if there is a number before and after the hyphen, as well as how many digits that number is. Let me play around with this and see what I can come up with.

Well if it helps, there shouldn't be any other hyphen in the cells. And it will always be the digits 1,2,3, or 4.

If on the rare occasion there is, I can do it manually, but there really shouldn't be any.

 

[MrKowz]

Try the following macro - I tested it on some dummy data and it seemed to work. Note that is begins on row 1 and will continue to the end of your data in column R. Expect it to take a while to run if you have a LOT of data (which it appears you do).

Public Sub AverageHyphens()
Dim i       As Long, _
    j       As Long, _
    LR      As Long, _
    tmp     As Variant, _
    tmp2    As Variant
    
Application.ScreenUpdating = False
LR = Range("R" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    Application.StatusBar = "Currently checking row " & i & " of " & LR
    tmp = Split(Range("R" & i).Value, " ")
    For j = LBound(tmp) To UBound(tmp)
        If InStr(tmp(j), "-") > 0 Then
            tmp2 = Split(tmp(j), "-")
            If IsNumeric(tmp2(LBound(tmp2))) And IsNumeric(tmp2(UBound(tmp2))) Then
                Range("S" & i).Value = Application.Average(CLng(tmp2(LBound(tmp2))), CLng(tmp2(UBound(tmp2))))
                Exit For
            End If
        End If
    Next j
Next i
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

 

[Z0827]

That literally works perfectly. I can't thank you enough, I do appreciate it.

Time to start reading my Excel Bible and learning more about coding these for situations like this. Thanks again.

 

[MrKowz]

That literally works perfectly. I can't thank you enough, I do appreciate it.

Time to start reading my Excel Bible and learning more about coding these for situations like this. Thanks again.

Awesome, glad it works for you! And thanks for the feedback.

The forums here are a fantastic resource to learn VBA (99% of my excel knowledge I attribute to the forums).

 

[Z0827]

Awesome, glad it works for you! And thanks for the feedback.

The forums here are a fantastic resource to learn VBA (99% of my excel knowledge I attribute to the forums).

Yes, I posted question a while ago and got an answer and kept poking around for information. I haven't need help since because of the information I can find, but hopefully I can return the favor and help someone else out one day.