We help IT Professionals succeed at work.

How to write a custom Excel 2016 function that gets first two characters of cell if numbers?

Grab First Two Numbers
If the cell's first three characters are 01-, I want them to be picked up.
If the cell is blank, I want the function to show 00-.
Example-of-Function-Desired.jpg
Comment
Watch Question

Try this
=IF(ISBLANK(B9),"00-",LEFT(B9,3))&IF(ISBLANK(C9),"00-",LEFT(C9,3))&IF(ISBLANK(D9),"00",LEFT(C9,2))

Author

Commented:
Sorry, I forgot to include Custom with function in the question.
Then you can use this

Function CombineText(rg As Range) As String
    Dim col As Integer
    If rg.Columns.Count = 3 And rg.Rows.Count = 1 Then
        For col = 1 To 3
            If rg.Cells(1, col) = "" Then
                CombineText = CombineText & "00"
            Else
                CombineText = CombineText & Left(rg.Cells(1, 1), 2)
            End If
            If col < 3 Then
                CombineText = CombineText & "-"
            End If
        Next col
    End If
End Function

Open in new window


For row 9: =CombineText(B9:D9)

Author

Commented:
I tried out the function, but when the 2nd column changed, the function didn't update.
Level2NotUpdated.jpg
Sorry, typing error.
Use this
Function CombineText(rg As Range) As String
    Dim col As Integer
    If rg.Columns.Count = 3 And rg.Rows.Count = 1 Then
        For col = 1 To 3
            If rg.Cells(1, col) = "" Then
                CombineText = CombineText & "00"
            Else
                CombineText = CombineText & Left(rg.Cells(1, col), 2)
            End If
            If col < 3 Then
                CombineText = CombineText & "-"
            End If
        Next col
    End If
End Function

Open in new window

Author

Commented:
Great, thanks!