• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 56
  • Last Modified:

How to change "01-","02-", to A, B in Excel 2016 custom function?

I had this question after viewing How to add a parameter to this custom function for the number of columns?.

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

In this function, the first three characters of cells are in the format "01-", "02-", etc. is picked up from cells from a number of columns.  I would now like to convert the "01-" to A, "02-" to B, etc.  There are no numbers above 26.  If a cell is blank, I would like its value to "X".
0
Alex Campbell
Asked:
Alex Campbell
  • 4
  • 2
1 Solution
 
Bill PrewCommented:
So you just want the result to be A-B-C-D-E, etc, that's all, no data from the cells themselves???


»bp
0
 
Bill PrewCommented:
If it needs to be sensitive to what the left three characters then try this...

Function CombineTextA(rg As Range) As String
    Dim col As Integer
    Dim txt As String
    If rg.Rows.Count = 1 Then
        For col = 1 To rg.Columns.Count
            If rg.Cells(1, col) = "" Then
                txt = "0"
            Else
                txt = Left(rg.Cells(1, col), 2)
            End If
            txt = Chr(Asc("A") + CInt(txt) - 1)
            If col = 1 Then
                CombineTextA = txt
            Else
                CombineTextA = CombineTextA & "-" & txt
            End If
        Next col
    End If
End Function

Open in new window


»bp
0
 
Alex CampbellAuthor Commented:
I am getting @ for blank cells instead of 0.
BlanksShowingAs-.jpg
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Bill PrewCommented:
Okay, got it, try this please:

Function CombineText(rg As Range) As String
    Dim col As Integer
    Dim txt As String
    If rg.Rows.Count = 1 Then
        For col = 1 To rg.Columns.Count
            If rg.Cells(1, col) = "" Then
                txt = "0"
            Else
                txt = Chr(Asc("A") + CInt(Left(rg.Cells(1, col), 2)) - 1)
            End If
            If col = 1 Then
                CombineText = txt
            Else
                CombineText = CombineText & "-" & txt
            End If
        Next col
    End If
End Function

Open in new window


»bp
0
 
Alex CampbellAuthor Commented:
Great! Thanks
0
 
Bill PrewCommented:
Welcome.


»bp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now