Avatar of Alex Campbell
Alex CampbellFlag for United States of America

asked on 

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

User generated image
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
Microsoft OfficeVBAMicrosoft Excel

Avatar of undefined
Last Comment
Alex Campbell
Avatar of Ejgil Hedegaard
Ejgil Hedegaard
Flag of Denmark image

Try this
=IF(ISBLANK(B9),"00-",LEFT(B9,3))&IF(ISBLANK(C9),"00-",LEFT(C9,3))&IF(ISBLANK(D9),"00",LEFT(C9,2))
Avatar of Alex Campbell
Alex Campbell
Flag of United States of America image

ASKER

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)
Avatar of Alex Campbell
Alex Campbell
Flag of United States of America image

ASKER

I tried out the function, but when the 2nd column changed, the function didn't update.
Level2NotUpdated.jpg
ASKER CERTIFIED SOLUTION
Avatar of Ejgil Hedegaard
Ejgil Hedegaard
Flag of Denmark image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Alex Campbell
Alex Campbell
Flag of United States of America image

ASKER

Great, thanks!
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo