How to create UDF to pull out Number Prefixes from Excel cell values?

Hi,

pls try

Function myUdf(rng As range) As String
res = Left(rng, 3)
If res = "" Then res "00."
myUdf = res
End Function

Select allOpen in new window

Regards

The code value should be the first three characters from each cell, such as: 01.02.

Did you really mean the first five characters?

---

»bp

You can do this with a formula like this:

=IF(F2<>"",MID(C2,1,3)&MID(D2,1,3)&MID(E2,1,3)&MID(F2,1,3),IF(E2<>"",MID(C2,1,3)&MID(D2,1,3)&MID(E2,1,3)&"00.",IF(D2<>"",MID(C2,1,3)&MID(D2,1,3)&"00.00.","")))

Select allOpen in new window

You don't need a UDF (User Defined Function) to accomplish this task that way you don't need to change this file to a macro-enabled file.

Function BuildCode(r As Range) As String
Dim cel As Range

For Each cel In r
    BuildCode = BuildCode & Left$(cel, 3)
Next
If BuildCode = "" Then
    BuildCode = "00."
End If
End Function

Select allOpen in new window

Usage in A2:
=BuildCode(C2:F2)

A Function that is already available in Excel will always be more efficient than the best code written for a UDF. inbuilt Functions acre compiled in C++

The following worked great for combining the codes, but it didn't do anything about the blank ones.

Function BuildCode(r As Range) As String
Dim cel As Range

For Each cel In r
    BuildCode = BuildCode & Left$(cel, 3)
Next
If BuildCode = "" Then
    BuildCode = "00."
End If
End Function

Please explain more about the "blank ones". My UDF as written will put "00." in column "A" if the values in that row in columns B to F are blank. If that's not correct then please explain.

That's it. Great!

You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you including one on writing UDFs called Creating your own Excel formulas and doing the impossible.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2016