Letter = Number

[KO]

When I type in certain letters, I need them to be displayed as certain
numbers, i.e., MJ = 01, HS = 02, SE = 03, etc. I'm not very good with
formulas, so any help is appreciated. Thanks in advance!

 

[Billy Liddel]

When I type in certain letters, I need them to be displayed as certain
numbers, i.e., MJ = 01, HS = 02, SE = 03, etc. I'm not very good with
formulas, so any help is appreciated. Thanks in advance!

One way, custom format the range as 00 and use AutoReplace, Replace MJ with
1 et al.

Regards
Peter

 

[Mike H]

Hi,

You could use auto Correct

Tools|Auto Correct Options

Create an Option to change MJ to 01 etc
Format the cells with a custom format of 00

Mike

 

[Rick Rothstein \(MVP - VB\)]

Are the letters you are typing in **always** two characters long? If not,
what is the longest number of characters you can type in? Are the numbers to
be returned **always** sequential listed (that is, could there ever be
gaps... 01,02,05,06,etc. for example)? Finally, what is the largest number
that will be returned?

Rick

 

[KO]

Yes, the letters are always two characters long.
No, the numbers are not always sequential, there are gaps.
The largest number so far is 25, I don't think I'll ever have number larger
than 99. Thank you.

 

[KO]

Thanks so much - this will work for what I need!

Hi,

You could use auto Correct

Tools|Auto Correct Options

Create an Option to change MJ to 01 etc
Format the cells with a custom format of 00

Mike

 

[Earl Kiosterud]

If you set up Autocorrect to change, e.g.: 02 to HS, , it will change such character
sequences typed elsewhere, including in other Office apps (Word, etc.).

If you number format the cells (Format - Cells - Number - Custom):

[=1]"MJ";[=2]"HS";General (you fill in the rest),

it will display MJ for 01, but also for 1, 001, etc.

Or it could be done with a macro which would change your 01 to MJ as soon as you typed it
and pressed Enter.

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

 

[KO]

Oh, I didn't realize you couldn't make it specific to certain columns.
I've tried the number format cells you suggested, but can't get it to work
correctly. Not sure what I am doing wrong.
If I do a macro, would it change in all cells or just in certain cells.
Sorry, I'm not very good at this.
Thanks.

If you set up Autocorrect to change, e.g.: 02 to HS, , it will change such character
sequences typed elsewhere, including in other Office apps (Word, etc.).

If you number format the cells (Format - Cells - Number - Custom):

[=1]"MJ";[=2]"HS";General (you fill in the rest),

it will display MJ for 01, but also for 1, 001, etc.

Or it could be done with a macro which would change your 01 to MJ as soon as you typed it
and pressed Enter.

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

Hi,

You could use auto Correct

Tools|Auto Correct Options

Create an Option to change MJ to 01 etc
Format the cells with a custom format of 00

Mike

 

[Earl Kiosterud]

KO,

Select your column(s), the Format - Cells - Number - Custom. In the "Type" box, type or
copy/paste the codes I gave you.

[=1]"MJ";[=2]"HS";General
Press Enter

This will work only for entering 1 or 2. When you get it working, you can add more sections
for 3, 4, etc, just like the ones for 1 and 2 that I gave you.

As for the macro, it could be set up to change the user's entry only for certain cells
(columns, etc.). You'll have to learn how to put a macro into a workbook. Then we can give
you the macro.
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

Oh, I didn't realize you couldn't make it specific to certain columns.
I've tried the number format cells you suggested, but can't get it to work
correctly. Not sure what I am doing wrong.
If I do a macro, would it change in all cells or just in certain cells.
Sorry, I'm not very good at this.
Thanks.

If you set up Autocorrect to change, e.g.: 02 to HS, , it will change such character
sequences typed elsewhere, including in other Office apps (Word, etc.).

If you number format the cells (Format - Cells - Number - Custom):

[=1]"MJ";[=2]"HS";General (you fill in the rest),

it will display MJ for 01, but also for 1, 001, etc.

Or it could be done with a macro which would change your 01 to MJ as soon as you typed it
and pressed Enter.

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

Hi,

You could use auto Correct

Tools|Auto Correct Options

Create an Option to change MJ to 01 etc
Format the cells with a custom format of 00

Mike

:

When I type in certain letters, I need them to be displayed as certain
numbers, i.e., MJ = 01, HS = 02, SE = 03, etc. I'm not very good with
formulas, so any help is appreciated. Thanks in advance!