How do I add letters to the end of totals?

R

ryangodammit

Probably very simple, but I'm new.

I need the total in one cell appear the same in another, but with a
'a' after it. ie 19 becomes 19a. If the original total already ends i
'a', it becomes 'b', and so on.

What is this called, and how can I do it?

Rya
 
N

Norman Jones

Hi Ryan,

If you want to use a custom function, paste the following code into a
standard module in your workbook (instructions - if needed - follow the
code):

'----------------------------------------------
'\\ Start of Code
Function abcADD(Cell As Range) As String
Dim suffix As String

If IsNumeric(Cell.Value) Then
suffix = "a"
ElseIf Asc(Right(Cell.Value, 1)) > 96 And _
Asc(Right(Cell.Value, 1)) < 122 Then
suffix = Chr(Asc(Right(Cell.Value, 1)) + 1)
Else
suffix = Right(Cell.Value, 1) & "a"
End If

If suffix = "a" Then
abcADD = Cell.Value & suffix
Else
abcADD = Left(Cell.Value, _
Len(Cell.Value) - 1) & suffix
End If
End Function
'\\ End of Code
'----------------------------------------------

Instructions (shamelessly lifted from a post by Dave Peterson)::

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel. by hitting Alt-F11

To use the function, type :

=abcADD(A5)

into the desired cell, where A5 is the cell containing your existing value.
 
Top