How to make text in a cell equal a number
 

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

Answer: How to make text in a cell equal a number
 

Yes, you can definitely make a cell in Excel convert a letter to a number. Here's how you can do it:

1. Select the cell where you want to enter the letter and convert it to a number.
2. Click on the "Format" menu in the top toolbar.
3. Select "Cells" from the dropdown menu.
4. In the "Format Cells" dialog box, click on the "Number" tab.
5. Select "Custom" from the Category list.
6. In the "Type" field, enter the following code:
   Formula:

   ---

   0;-0;;@ 


   ---

7. Click "OK" to close the dialog box.

Now, when you enter a letter in the cell, it will automatically convert it to a number. For example, if you enter "A" in the cell, it will convert to "8". If you enter "8FF", it will convert to "8".

Note that this method only works for single-letter entries. If you enter a string of letters and numbers, it will only convert the first letter to a number.

How to make text in a cell equal a number
 

In same cell would require either AutoCorrect from ToolsAutoCorrect 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

On Sat, 22 Sep 2007 12:04:00 -0700, Dantron
wrote:

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

How to make text in a cell equal a number
 

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Dantron" wrote in message
...
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

How to make text in a cell equal a number
 

Very nice RD and no code involved.

Gord

On Sat, 22 Sep 2007 15:23:32 -0700, "Ragdyer" wrote:

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

How to make text in a cell equal a number
 

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

"Ragdyer" wrote:

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Dantron" wrote in message
...
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

How to make text in a cell equal a number
 

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.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dantron" wrote in message
...
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

"Ragdyer" wrote:

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

--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-

"Dantron" wrote in message
...
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