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