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

Alex Campbell
Alex Campbell used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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)
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial