I'm currently working my way through a series of data tables showing minimum and Maximum values. The cell values are a mix of Temp values to 1 decimal point followed by the date.

for example 21.93rd, or 24.817th. and I need to clean the data by inserting a space and an opening parentheses after the 4th character, and a closing parentheses at the end of the string.


Later on down the table, the data boffins started throwing in some negative numbers just to test if I was paying attention. The following code should work shouldn't it?

Option Explicit


Sub AddParenthesesWithSpace()
    Dim rng As Range
    Dim cell As Range
    Dim strVal As String
    ' Set the range to B3:N27
    Set rng = ThisWorkbook.Sheets("Sheet3").Range("B3:N27")
    ' Loop through each cell in the range
    For Each cell In rng
        ' Get the string value from the cell
        strVal = cell.Value
        ' Check if the string has at least 4 characters and is positive or negative value
        If Len(strVal) >= 4 And Left(strVal) = "-" Then
            'Insert space and open parenthesis after the 5th Character
            strVal = Left(strVal, 5) & " (" & Mid(strVal, 6)
            ' Add closing parenthesis at the end
            strVal = strVal & ")"
            ' Update the cell value
            cell.Value = strVal
        Else
            ' Insert space and open parenthesis after the 4th character
            strVal = Left(strVal, 4) & " (" & Mid(strVal, 5)
            ' Add closing parenthesis at the end
            strVal = strVal & ")"
            ' Update the cell value
            cell.Value = strVal
        End If
    Next cell
    ' Clean up
    Set rng = Nothing
End Sub