How to Create a Custom Text Translation(?)

G

Gary_at_TMI

New Member
Joined
Aug 31, 2007
Messages
5
I'm really not sure what/how to ask this question, but I have not been able to find anything that is similar to my problem. If this question has already been posted and answered, a link to that post would be perfect.

Basically, I am wondering how I can enter in a short code into a cell, but have it displayed as "Human Friendly" text.

i.e.
I would type into a cell COGS, and when I press enter, or move to a different cell, "Cost of Goods Sold" would be displayed in that cell. The value of the code would not be changed, is would be display as the full text version when not editing the cell,

I have 13 codes currently that I would like displayed more human friendly:

BANK = Bank
CCARD = Credit Card
COGS = Cost of Goods Sold
EQUITY = Equity
EXEXP = Other Expense
EXINC = Other Income
EXP = Expense
FIXASSET = Fixed Asset
INC = Income
LTLIAB = Long Term Liability
OASSET = Other Asset
OCASSET = Other Current Asset
OCLIAB = Other Current Liability

I would also like to be able to easily expand/modify the list of valid codes and displayed text if possible.

Any help or suggestions would be greatly appreciated.

TIA
 
Last edited:

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Place it in ThisWorkbook module.
Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    Dim arrShort As Variant, arrLong As Variant, i As Integer
    
    arrShort = Array("BANK", "CCARD", "COGS", "EQUITY", "EXEXP", "EXINC", "EXP", "FIXASSET", _
                     "INC", "LTLIAB", "OASSET", "OCASSET", "OCLIAB")
    
    arrLong = Array("Bank", "Credit Card", "Cost of Goods Sold", "Equity", "Other Expense", "Other Income", _
                    "Expense", "Fixed Asset", "Income", "Long Term Liability", _
                    "Other Asset", "Other Current Asset", "Other Current Liability")
                    
    
    Application.EnableEvents = False
    
    For i = 0 To 12
        If arrShort(i) = Target.Value Then
            Target.Value = arrLong(i)
            Exit For
        End If
    Next
    
    Application.EnableEvents = True
    
End Sub
 
Upvote 0
Gary_at_TMI said:
The value of the code would not be changed, is would be display as the full text version when not editing the cell
Click to expand...
I think this is the significant bit: OP wants the full text version displayed normally but only the short code when in edit mode.

A cell can't have two different values in it at the same time. You would have to enter and edit the short code in one cell and then use a lookup function to display the full text version in a different cell.

Agreed?
 
Upvote 0
Ruddles said:
I think this is the significant bit: OP wants the full text version displayed normally but only the short code when in edit mode.

A cell can't have two different values in it at the same time. You would have to enter and edit the short code in one cell and then use a lookup function to display the full text version in a different cell.

Agreed?
Click to expand...

I concur.
 
Upvote 0
Gary, do you have a small arrea where you can store a lookup table and a spare cell next to the short code where the full text version can be displayed?

Try this: create a new worksheet called LOOKUP and copy this table into it:-

<TABLE style="WIDTH: 194pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=258 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4754" width=130><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17></TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" width=64>
A​
</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 98pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" width=130>
B​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>
1​
</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">BANK</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Bank</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>
2​
</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">CCARD</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Credit Card</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>
3​
</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">COGS</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Cost of Goods Sold</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>
4​
</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">EQUITY</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Equity</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>
5​
</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">EXEXP</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Other Expense</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>
6​
</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">EXINC</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Other Income</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>
7​
</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">EXP</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Expense</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>
8​
</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">FIXASSET</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Fixed Asset</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>
9​
</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">INC</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Income</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>
10​
</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">LTLIAB</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Long Term Liability</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>
11​
</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">OASSET</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Other Asset</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>
12​
</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">OCASSET</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Other Current Asset</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>
13​
</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">OCLIAB</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Other Current Liability</TD></TR></TBODY></TABLE>

Then in your main sheet, if your short code is going to be entered and edited in cell A1 and you want the full text version in A2, enter this formula in A2:-
Code: 
=IF(ISNA(MATCH([COLOR=red][B]A1[/B][/COLOR],[COLOR=blue][B]LOOKUP[/B][/COLOR]!A:A,0)),"",VLOOKUP([COLOR=red][B]A1[/B][/COLOR],[COLOR=blue][B]LOOKUP[/B][/COLOR]!A:B,2,0))

Now enter a short code in A1.

Howzat?

The colour coding indicates what you should change if you decide for a different name for the lookup worksheet or if the short code goes somewhere other than A1. (Obviously once the formula is working it can be copied down to other rows.)
 
Upvote 0
The best and easy method in excel 2007 is use auto correct in excel options under proofing, click auto correct options, then type the what ever expansions you want.

This same will be available in ms-word also
 
Upvote 0
Does this do what the OP specified?
Gary_at_TMI said:
The value of the code would not be changed, is would be display as the full text version when not editing the cell
Click to expand...
OP wants the full text version displayed normally but the short code displayed when in edit mode.
 
Last edited:
Upvote 0
You could have a lookup table, and use that to apply a custom cell format using a worksheet change event, so that the display looks like the text, but the contents remain as the code. Have a table like this in one sheet, with named ranges for the column areas as Code and Text:
Excel Workbook
AB
1CodeText
2BANKBank
3CCARDCredit Card
4COGSCost of Goods Sold
5EQUITYEquity
6EXEXPOther Expense
7EXINCOther Income
8EXPExpense
9FIXASSETFixed Asset
10INCIncome
11LTLIABLong Term Liability
12OASSETOther Asset
13OCASSETOther Current Asset
14OCLIABOther Current Liability
Sheet12


and in the worksheet code area of the editing sheet, have this code:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    For Each c In Target
        result = Application.Match(c.Value, Sheets("Sheet12").Range("Code"), 0)
        If Not IsError(result) Then
            c.NumberFormat = ";;;""" & Sheets("Sheet12").Range("Text").Cells(result) & """"
        Else
            c.NumberFormat = "General"
        End If
    Next
End Sub

.. and it should do what is required. ( not that an auditor would be very happy at the result, I'm sure! )

Normal display mode=
Excel Workbook
A
1Cost of Goods Sold
2Income
Sheet11


... and in formula display mode:
Excel Workbook
A
1COGS
2INC
Sheet11


( which is what the cell contents actually are ).
 
Upvote 0
hatsoff.jpg


Yoink! Code saved away for future use!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Can this code be shortened?
2
Replies
15
Views
748
julhs
J
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
S
  • Question Question
Circular iterations and using the max function.
Replies
0
Views
696
Satterfield
S
N
  • Question Question
Userform combo box - dynamic range based on category in another column
Replies
0
Views
2K
nzclogger1
N
N
  • Question Question
How to speed up macro
Replies
1
Views
341
CharlesChuckieCharles
C

Forum statistics

Threads
1,224,613
Messages
6,179,903
Members
452,948
Latest member
Dupuhini

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top