Data cleaning temperature data where a value might be negative

[Paul_Hossler]

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.

If it were me, I'd consider putting the temp in one column and the date made into a real Excel date in another

Capture.JPG

I think having the pieces separated would provide more flexibility

Personally, I like to use array entered UDFs, but you could include the code in a sub for a once-and-done.

Only problem (and it might make this not usable) is the date part lacks a month, so I just said that if it's larger than today, it must be last month

But it was an interesting question in any event

Option Explicit


Function SplitTempDate(s As String) As Variant
    Dim A(0 To 1) As Variant
    Dim V As Variant
    Dim d As Long, m As Long, y As Long
    
    's = -12.34th
    V = Split(s, ".")   '(0) = -12 (1) = 34th
    
    A(0) = CDbl(V(0) & "." & Left(V(1), 1))  '   =   -12.3
    A(1) = CDbl(Mid(V(1), 2, Len(V(1)) - 3)) '   =   4
    
    d = Day(Now)
    m = Month(Now)
    y = Year(Now)
    
    'if A(1) > today then it must be last month
    If A(1) > d Then
        A(1) = DateSerial(y, m - 1, A(1))
    Else
        A(1) = DateSerial(y, m, A(1))
    End If
    
    SplitTempDate = A
End Function