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

# 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
• 4
• 2
1 Solution

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

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

»bp
0

Author Commented:
I am getting @ for blank cells instead of 0.
BlanksShowingAs-.jpg
0

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

»bp
0

Author Commented:
Great! Thanks
0

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