How to make text in a cell equal a number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

If I want to type a letter in a cell, can I have that cell convert the letter
to a number ?example, type in A1 the letter A and have A1 return the number
8, or 8FF I am using Excel 2003
 
In same cell would require either AutoCorrect from Tools>AutoCorrect Options or
event code.

How many letters are you speaking of?

If using Autocorrect I would suggest something like ax, bx, cx, dx etc.

For event code.....................

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A100")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J")
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 23, 15)
For Each rr In r
inum = 0
For i = LBound(vals) To UBound(vals)
If UCase(rr.Value) = vals(i) Then
inum = nums(i)
End If
Next
If inum > 0 Then
rr.Value = inum
End If
Next
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.

Adjust Set r = Range("A1:A100"), vals and nums to suit.

Note: if wanting 8FF, enter double quotes around nums values as in "8FF"


Gord Dibben MS Excel MVP
 
You can try using a named formula.

From the Menu bar:
<Insert> <Name> <Define>

In the "Names In Workbook" box, enter:
A
In the "Refers To" box, change what's there to:
=8
OR
="8FF"

Then click <OK>

NOW, type
=A
OR
=a
in any cell, and you'll get your
8 or 8FF

If you chose to use the number
8
You could use it in calculations, such as:
=25*A
to get a return of 200

If you used the 8FF,
="The part number is "& A
to get a return of:
The part number is 8FF
 
Thankyou RD, it works perfectly well, the only thing that would work better
is if I could actually conditional formatt a cell to return a numerical
value, or a combination of numbers and text. It's nice that the other
respondant complimented you on your solution.

Dantron
 
I don't quite follow exactly what you're asking to accomplish.

Care to elaborate with some examples?

As to Gord's comment ... Gord knows a heck of a lot more then I do, it's
just that sometimes one thinks of something that someone else might not
have.

I believe it was Gord who introduced me to named formulas several years ago.
 
Back
Top