To replace a letter with a number in Excel, use SUBSTITUTE: with the text to change in A2, enter =SUBSTITUTE(A2,"A","1") in another cell to change every uppercase A to 1. If you mean convert a letter to its alphabet position, such as A to 1 or B to 2, use a different formula; the right method depends on what “substitute” means in your data.
First decide what you want to change
There are two common tasks behind the phrase “substitute number for letter.” One is editing text: replace a particular character wherever it appears, such as changing every A in a product code to 1. The other is converting letters into their alphabet positions, such as turning A into 1 and Z into 26. Excel handles these differently, so identify the desired result before choosing a formula.
For a direct character replacement, SUBSTITUTE is usually the simplest choice. It changes matching text inside a cell without regard to where the match occurs. For a letter-to-position conversion, a formula based on the character code can work when each cell contains one English letter. Neither operation automatically understands a custom code: if your system says that A means 7 and B means 3, you need to specify those mappings.
It is also possible that you want to replace a number with a letter, rather than the other way around. A lookup table is usually clearer for that job, especially when the mapping is not the standard A=1, B=2 sequence. The examples below distinguish literal replacement, alphabet numbering, and custom mappings.
Replace a letter with a number using SUBSTITUTE
Suppose A2 contains the text ABACA and you want every uppercase A replaced with the digit 1. With that cell layout, enter =SUBSTITUTE(A2,"A","1") in B2. The result is 1B1C1. The formula leaves the source cell unchanged, so you can compare the original and revised text or copy the results elsewhere.
SUBSTITUTE takes the original text, the text to find, the replacement text, and an optional occurrence number. If you leave out the occurrence number, Excel replaces every matching occurrence. If you supply it, Excel replaces only that numbered occurrence, counting from left to right. This is useful for changing one separator or one repeated character without changing the others.
For example, with ABACA still in A2, this formula changes only the second A that appears in the text: =SUBSTITUTE(A2,"A","1",2). The result is AB1CA. The occurrence number refers to the second match, not the second character in the cell.
The replacement can be more than one character. For example, you can replace a hyphen with a slash or replace a short text label with a longer one. The function returns the full revised text; it does not alter the original cell or change the formatting of the source.
Change several different letters
For a small number of replacements, nest SUBSTITUTE functions. With A2 containing A-B-C, this formula changes A to 1 and B to 2: =SUBSTITUTE(SUBSTITUTE(A2,"A","1"),"B","2"). The inner function makes the first change, and the outer function works on that result. The final value is 1-2-C.
Nested formulas can become hard to read when there are many mappings, and their order can matter. For example, if one replacement creates text that a later replacement also targets, the later step can modify the newly inserted text. Keep a written list of the intended mappings and test the formula on representative values before filling it down a large column.
If you have many substitutions or expect the rules to change, store the mappings in a two-column table instead of embedding every rule in a long formula. A table is easier to review and update. For complex text that contains many different letters, a helper column, a carefully designed lookup process, or Power Query may be more maintainable than a chain of nested functions. The best choice depends on whether you are replacing whole values or individual characters within longer strings.
Convert a single letter to its alphabet number
If each cell contains one English letter and you want A=1 through Z=26, you can calculate its position in the alphabet. With a single letter in A2, use =CODE(UPPER(A2))-64. For A, the result is 1; for B, it is 2; and for Z, it is 26. UPPER makes the formula handle lowercase letters as well, so a lowercase c also returns 3.
This method is intended for the 26 basic English letters, not for a phrase, accented letters, punctuation, or a custom alphabet. CODE returns a character code, and subtracting 64 works for uppercase A through Z because their codes are consecutive in the character set used by Excel. Check that the cell really contains one letter; spaces or other characters can produce a number that is not an alphabet position.
The reverse conversion is also possible for values from 1 to 26. With a number in A2, use =CHAR(A2+64): a value of 1 returns A, and a value of 26 returns Z. This formula is not a complete system for Excel-style column labels: after Z, those labels continue as AA, AB, and so on. It also does not validate the input, so values outside 1 through 26 will not produce the intended alphabet mapping.
Understand text results and numeric values
A digit inserted by SUBSTITUTE is replacement text. If the formula changes A23 to 123, the result is still text, even though it looks like a number. That distinction matters if you plan to add, sort numerically, or use the result in calculations. Text that looks numeric may sort differently from numeric values and may not behave as expected in arithmetic.
If the entire result should be a number, wrap the replacement in VALUE. With A2 containing the text A23, enter =VALUE(SUBSTITUTE(A2,"A","1")); the result is the numeric value 123. This works only if the completed replacement produces text Excel can interpret as a number. If letters remain, or the text uses an incompatible decimal or thousands separator, VALUE may return an error or interpret the text according to the workbook’s regional settings.
Keep the result as text when it is an identifier, code, account reference, or other value where digits are labels rather than quantities. Converting such values to numbers can remove leading zeros or change how they are handled. Decide based on the meaning of the data, not just on whether the result looks numeric.
SUBSTITUTE versus REPLACE and Find and Replace
SUBSTITUTE searches for matching text. By contrast, REPLACE changes characters at specified positions, whether or not those characters match a particular letter. With A2 containing ABACA, =REPLACE(A2,2,1,"1") replaces one character starting at position 2, producing A1ACA. Use it when the position is what matters; use SUBSTITUTE when the character or text to find is what matters.
For a one-time edit, Excel’s Find and Replace command may be faster than a formula. In Excel for Microsoft 365 on Windows, select the cells you want to edit, then choose Home > Find & Select > Replace and enter the text to find and its replacement. The selection matters: if a range is selected, the replacement can be limited to that range; if you intend to work on a whole worksheet, make sure you have not restricted the selection unintentionally. Excel for Mac also has Find and Replace, although the exact menus and keyboard shortcuts can vary by release.
A formula is preferable when you need to preserve the original values, repeat the transformation as new data arrives, or make the rule visible to someone reviewing the workbook. Find and Replace edits cells directly, so keep a backup or copy the data first when the original text may be needed later.
Common problems and how to avoid them
- Nothing changes: Check that the old text matches the cell, including spaces and capitalization.
SUBSTITUTEis case-sensitive, so searching for uppercase A does not match lowercase a. Use an appropriate case-normalizing step if both forms should be treated alike. - More occurrences change than intended: When the optional occurrence number is omitted, every matching instance is replaced. Add the occurrence number if you intend to change only one match.
- The result is not a number: A replacement that looks like a digit is still text. Use
VALUEonly when the entire result is a valid number and should be used arithmetically. - A formula is displayed instead of a result: Check that the cell is not formatted as Text, that the formula begins with an equals sign, and that Show Formulas is not turned on. If a cell was already formatted as Text, changing the format alone may not recalculate an existing entry; re-enter the formula.
- Results differ for upper- and lowercase letters:
SUBSTITUTEdistinguishes case. Normalize the source withUPPERorLOWERwhen that suits the task, while remembering that doing so changes the case of all text passed through that step.
Also check for invisible spaces, copied punctuation, or inconsistent source formats. A cell that appears to contain A may have a space before or after it, and an exact search will not treat that as the same text. Test a few examples that include ordinary values and edge cases before applying the formula to an entire dataset.
Choose a method for a repeatable workflow
Use SUBSTITUTE for a repeatable find-this-text-and-change-it rule, REPLACE for a change based on character position, and Find and Replace for a quick one-time edit. Use a character-code formula only for a single letter in a standard English A-to-Z mapping. For nonstandard mappings, keep the rules in a visible table so they can be checked and maintained.
For a dependable workflow, keep the source data intact, put the formula in a separate result column, and verify several outputs before copying it down. Confirm whether the desired output is text or a true number, whether capitalization matters, and whether every occurrence or just one should change. These checks prevent the most common errors and make it easier to explain the transformation later.
Frequently Asked Questions
How do I replace every letter A with 1 in Excel?
With the text in A2, enter =SUBSTITUTE(A2,"A","1") in another cell. It replaces every uppercase A in that cell. The function is case-sensitive, so lowercase a is a separate match.
How do I replace only the first occurrence?
With the original text in A2, use =SUBSTITUTE(A2,"A","1",1). The final argument tells Excel to replace only the first matching A. Change that argument to 2 to replace only the second match.
How do I convert A to 1 and B to 2?
For one English letter in A2, use =CODE(UPPER(A2))-64. It returns the letter’s position from 1 to 26. This is not a direct replacement for a whole word; each character in a longer string needs to be processed separately.
Why does Excel treat my replaced number as text?
SUBSTITUTE returns text, including when its replacement is a digit. If the entire result is a valid number and you need to calculate with it, convert it with VALUE. Keep it as text if it is an identifier or must retain leading zeros.
Should I use SUBSTITUTE or REPLACE?
Use SUBSTITUTE when you know the character or text to find, regardless of its position. Use REPLACE when you know the position and number of characters to overwrite. For a quick manual correction, Find and Replace may be more convenient.
Related
- Real Estate Excel Budget Template: Setup and Costs
- Randomize Numbers in Excel Without Duplicates
- Rank in Excel: How to Rank Values Largest to Smallest
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Sum Based on Criteria with SUMIF and SUMIFSIf categories are in A2:A7 and amounts are in B2:B7, use =SUMIF(A2:A7,"East",B2:B7) to add the amounts for East;…
- Excel Running Total: Formulas, Tables, and TroubleshootingTo create a running total in Excel, enter your amounts in B2:B7, type =SUM($B$2:B2) in C2, and fill…
- Excel Filter Cells Containing Specific TextTo filter Excel rows for cells containing particular text, select a cell in your data, choose Data >…
- How to Extract One Word From a Cell in Excel With FormulasTo extract a particular word from text in Excel for Microsoft 365, put the text in A2 and…
- Calculate Age in Excel as of Today: Formulas That WorkTo calculate age in completed years as of today, put each person’s date of birth in A2 and…
- Excel LEN Function: Count Characters in Cells EasilyTo count the characters in a cell, put the text in A2, select another cell, and enter =LEN(A2).…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.