We help IT Professionals succeed at work.

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

on

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

## View Solution Only

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

Commented:
Sorry, I forgot to include Custom with function in the question.

Commented:
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
``````

For row 9: =CombineText(B9:D9)

Commented:
I tried out the function, but when the 2nd column changed, the function didn't update.
Level2NotUpdated.jpg
Commented:
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
``````

Commented:
Great, thanks!