Email:      Pass:    Pass?

Free Excel Forum

How To Add A Letter In Front Of Each Number In A Cell

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I am working on this project & I cant figure out how to add a letter in
front of each number in a cell without clicking in to each individual cell
and typing it in. (very time consuming and tedious) The problem is - it's not
all the cells but a majority, all of the numbers are in the same column. To
explain, these are documents in storage. Each item is assigned a number (not
in any particular numerical order but they have to stay in the order they are.



The last three numbers need the X in front of them.

Any ideas? Thanks so much!!

I read this previous reply and I'm not getting it.......I'm Excel challenged

Use a help formula


copy down/across, then copy and paste special as values in place ,
finally replace the old values with the new


Peo Sjoblom

Similar Excel Video Tutorials

Helpful Excel Macros

Replace Formulas with Values (For The Entire Workbook)
- This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Format Cells in The Number (Numerical) Number Format in Excel
- This free Excel macro formats selected cells in the Number or Numerical number format in Excel. This means that the cel
Sort Data that Doesn't Have Headers in Descending Order in Excel
- Sort data in Excel that doesn't have headers using a macro. This Excel macro assumes that your data set does not have a

Similar Topics

Hi Everybody

I have an Excel Sheet "Sheet1" in Column A from Row 6 down to Row 140 I have
sequential numbers from 1 to 135. In Column B again from Row 6 down to Row
140 I have the single letters E, W, N in no particular order. Is it possible
to have a routine that will place a number 1 in frint of the First letter E
it finds e.g. 1E and when it finds the next E it places 2 in front of it e.g
2E and it would continue until it finds the last letter E placing the
sequential number in front as per the example. The routine would then
continue and do the same for the other 2 Letters N, W and begin each with the
number 1.

Hope I've made myself clear

Many thanks


Hi everyone.

This should be a simple question.

In column A there is a list of numbers.

for example:
A1 has 1378130
A2 has 1378249
A3 has 1378881
A4 has S016957
A5 has 1377540
A6 has S016745
A7 has 1379059


What formula can i put in cell B1 that will look at A1:A150 that can tell me the number of cells in that range that have the letter "S" in front of the number ? In this example the answer should be two.

Any ideas ?

I need to add one column into another..

i have a Column of numbers that all need to have a letter in front of them;
right now: 1, 10, 13, 14...etc
needs to be PS1, PS10, PS13, PS14..etc

The column does not go in numerical order so i cannot just drag the letter down. Is there a way to add these letter w/o having to copy and paste it into every row? Please help, i am trying to make a deadline!

I need to add one column into another..

i have a Column of numbers that all need to have a letter in front of them;
right now: 1, 10, 13, 14...etc
needs to be PS1, PS10, PS13, PS14..etc

The column does not go in numerical order so i cannot just drag the letter down. Is there a way to add these letter w/o having to copy and paste it into every row? Please help, i am trying to make a deadline!

I use Excel 07 with Windows XP. I need to display an equation in a cell and have it just show the total. Fox example, the cell with show 100,000 and when you click on the cell you will see =25+50+10+15. What I am doing now is a get updated figures in pdf format every day with around 100 items so I copy and paste the data into notepad and then open it in Excel. My first sheet gets the data in order. Then on the next sheet I organize the data into 10 categories. Let's say that I have in column A, rows 1-4, I have 25, 50, 10, and 15. Then in cell B1, I have a formula, =A1+A2+A3+A4. Then under that in column B2 I have a macro that copy cell A1 and pastes it special as values. The macro does this for all 10 of my categories. Some categories have 4 values, some have 10. So in cell B2, I would have 25+50+10+5 but there will be know equals in the front. As of now, I just go through all the categories and put an equals sign in front of all the strings. Then I copy the strings and paste special as formulas into the columns of my report that I give to my managers each day. They want to be able to see the values in the cell when they look. Does this make sense? Can I have a macro that adds an equals in front of each string? The problem I have when I tried to do the macro to add the equals is the next day when the macro runs, the formula ends up having yesterday's values instead of the new values from this day.

Good Afternoon -- thanks for looking

I have a worksheet to keep track of products. I use an SKU column with a Unique Number to keep track of those items on the shelves.

When I started my project I never imagined that my database would grow as large as it has. I started my SKU numbers with 80000, never suspecting that I would get to 90000. I am now at 125700. Many items have been removed / sold so it only encompases only 15500 rows.

On the site that I sell these Items, the SKU's when displayed start with 100000 and go to 125700 where 80000 is next and goes to 99999. ?? ( or the reverse depending on which tab I choose ) Not sure why this is but there is nothing I can do to change the way they do it so I must find a way to change my system. With all the 80000 - 99999 items numbered - changing them to 6 digit 125700+ numbers would be a huge undertaking so I would like to add a 0 in front of each 5 digit Number in my SKU Column. That way my items will show 080000 - 125700 instead of starting in the middle.

I do keep the column my ascending order so it is currently formated as a Number. I do at times copy an paste or pull ranges items by SKU numbers to mark down or modify.

When I place a 0 manually in front of 80000 - it disappears when I move from the Cell.

If I change it to a TEXT cell - it stays in place.

Excel help doesnt answer my dilemma -- nor does my book.

Can anyone help me to figure out what the best thing to do is and how to accomplish it.

I see there are masks etc -- or is just text OK ? ( as I stated - I do use an numbered order or range to identify groups of items at times ) - if text is OK, what is the best way to add a 0 to the start of each 5 digit number other than individually ?
There are Gaps in my sequence so I cannot just replace the first cell with 080000 and pull down.

Its a lot of questions but I wanted to explain what it is I'm trying to do.

Thanks for looking

I get a descending order when I have letter in front of the numeral, but when I try just the number, all it gives me is a copy of the number.
Column Cell, input
a1, a1
a3, if I dragged down a2 will give a3, ect.
But if I input just numbers all it does is copy the last number.
Any have a solution for me?

i am sure this is probably really easy......i have to copy and paste values from an sap program over to excel spreadsheets, and I usually do about 15 at a time that end up in a column: 15 different cells. The value I am copying are ID numbers that all begin with zero and excel automatically removes the zeros at the front of each number.

Is there a formula/process for preventing this.

Thanks guys!


I'm trying to do this for ages.
Basically, in sheet1, column D, I have a list of names and numbers as follow:

bear - 035888877 MS - 02555887 Coal - 58887441 SE - 02587410

In Sheet2, column Q, I want to extract the number and keep the zeros in front (in case there is a zero). I've tried,
=REPLACE(LEFT(D1,LOOKUP(10,MID(A1,ROW(INDIRECT("1:30")),1)+0,ROW(INDIRECT("1:30")))),1,MIN(FIND(0,SUBSTITUTE(D1&0,{1,2,3,4,5,6,7,8,9},0)))-1,"")+0 (I found it online).
But I cannot keep the zeros in front! I tried changing cell format to text, didn't help either. I cannot do cutom-0000000 as the lenth of the number can change.

I'm trying to build a macro (recording) doing this but the pb is always the little 0. Please advise if you have an idea?

Many thanks. Fak

i have a spreadsheet with lots of numbers downloaded from our AS$)) system.
unfortunately, the columns have valid numbers and numbers like "******.**CR"
(* = a valid number).

i cannot get excel to convert the "CR" at the end of the number to a minus
sign in the front of the number.

any ideas?

Hi all,

I've looked high and low for the answer to this but just can't find it.

I've got a whole heap of GPS waypoint data I have to manage each week. After bringing them into Excel I need to simply put one letter in front of the unique id numbers in one column. I can do it by changing the first cell and dragging it down but because the numbers are not always in sequence I need to be able to just highlight the whole column and somehow put a letter in front of each number in each cell WITHOUT losing that unique number.

Can anyone help this very frustrated Kiwi ??



i am trying to use a wildcard with numbers but it will not work unless i put a letter in front of the number Below is the formula i am using. Ast you can see N814 is the numer i am using. i am doing a search and sum. If there is a any combo of numbers in column A between 8140 and 8149 it sums column D that match the number. however, as stated i have to put the letter in front or it does not find the number.

=SUMIF('SECTION 4'!A117:'SECTION 4'!A159,"N814?",'SECTION 4'!D116:'SECTION 4'!D159)

Hello there,

The Encrypting.xlsm worksheet is a project that encrypts text, turning letters into numbers, numbers that are produced by assigning a key that provide variables to complex excel calculations.

Its purpose is to automatically "encrypt" or translate letters to words or vice-versa, according to the key used.

I need a button in the "Encryptor" sheet that is assigned with a macro that when triggered:

Will take the text, letter by letter, within a cell and paste the equivalent number for each letter into another cell.

And.. Will do the reverse process simultaneously: Take the encrpyted message from another cell, number by number (separated by a space), and paste the equivalent letter for each number into another cell.

The numbers equivalent for each letter are shown on column D from row 2 to row 28, in the "number" sheet.

I had a tough time imagining how would this project work and spent some additional effort preparing the formulas. All I need is the macro, which is quite a challenge.

If you could lend me a hand on the macro it would be great, and you'll have an encryption worksheet for your own!

I have multiple (incredible number of) references that need to be made across different worksheets.

Example: Right now, cell B1 contains: ='[07-01-09.xls]FRONT'!$B$16

Now, if B2 contained: =B1

Then I could click on the bottom right of the cell, and copy it to the right perpetually. I could stop at column J, and the formula would read: =J1

Obviously, this doesn't work the same way.

I would LIKE for C1 to contain: ='[07-01-09.xls]FRONT'!$B$17

D1 to contain: ='[07-01-09.xls]FRONT'!$B$18

etc, on through to: ='[07-01-09.xls]FRONT'!$B$49

I would like to find an easy way to do this, as I now have to track assignments, and have to back-track to the beginning of the year.

This is going to result in 365 days worth of tracking 25+/- individuals, so I'm thinking about doing 12 workbooks (1 per month), with, of course, 30+/- days worth of tracking per.....either way....

Going to be rather a pain in the ***, but hey.

Is there a way to formulate something to the effect of...

...say this is in B1: ='[07-01-09.xls]FRONT'!$B$16

Can something be done in C1 that 'adds' a row to the formula in B1?

(='[07-01-09.xls]FRONT'!$B$16 + 1 row)

Sounds much more simplistic than it likely is, but I have to ask.

Thanks for any help!

Ok I am typing in a bunch of UPC numbers for my data sheet, It will be a
couple hundred numbers. The UPC numbers are as follows

For some odd reason all the numbers without a zero are wrong. All of these
numbers need 1 number 0 in front of them. As for the other numbers I get a
little green arrow in the upper left corner of the cell. It tells me
Something about "Number stored as text" (This is the one that is
automatically highlighted when i click the error tag) it gives me a couple
other options such as "Convert to Number" "Help on this Error"(Gets me no
where) "Ignore Error" which seems to be the only thing that allows the number
0 to stay. If I do the convert to number it removes the number 0. How can I
set the entire cell column to leave the number 0 infront of the number. I
have spent 45 minutes now trying to figure this out. Thanks for any help you
can provide me.

Hi all,

I attach here an Excel file where I ask you help to do something.

As you can see, in column AO I have a series of numbers. What I would like to do, is a VBA code that could read that number, and if that number is the same that the row number: Ok, don't do nothing. If that number is different from the row number then, it should copy all values from the "green-number" row to the row where that number is. A little bit confused right?

An example:

Read AO46. The number read is 48. Then It should copy all values from row 48 to row 46.


Read AO47. The number read is 49. Then it should copy all values from row 49 to row 47.


Read AO48. The number read is 52. Then it should copy all values from row 52 to row 48.


And this until the end of the sheet. Can someone give me a help out?

Thank you all

I need a formula that will look at multiple figures but will assign a letter for a particular number. I'd rather not use a macro.

my example, Less than 50% will have the letter 'E' assigned to it.
Then it needs to look for numbers between 50% and 65% will be assigned a 'D'.
Then it needs to look for numbers between 65% and 75% will be assigned a 'C'.
Then it needs to look for numbers between 75% and 85% will be assigned a 'B'
and anything greater than 85% will have a 'A' assigned to it.

It needs to look at all of this and when it has the figure it needs to assign the letter in another cell. it only has to look in one cell for the figure.

I have the IF formula for them individually eg =IF(A1<50%, "e", "") but that only assigns either a number or blank.

Any help would be greatly appreciated.

Thank you

I'm using Excel 2000 to maintain a membership list containing details
such as first name, last name, address, age, gender, ethnicity etc.
This document is kept up to date on a daily basis and at the end of
each month I transfer some of the details on to a front sheet that
tracks month-by-month totals e.g.

Jan Feb Mar
Male 150 181 168
Female 132 156 158
etc, etc

So far I've tried a couple of methods to pick up the info for the
front sheet:
1. Referring the cells on the front sheet to a total on a pivot table
held on another worksheet
2. Using the COUNTIF function

Both methods work OK but each month I have to copy the formulae into
the new month's column and then copy the previous months figures and
paste special into the previous months column as values only.
I am about to pass this spreadsheet on to someone else to maintain and
if they forget to do the "paste special" bit, then the previous
months column will display the current months figures.
I've tried recording a macro but it repeats in the same columns. I
need it to move along a column each month, is there a simple way to do
this (my knowledge of VB is zero)

Thanks for your help

Jacob Thomas

This will be pretty simple for some of you but I've never had to use this formula before so I don't know where to begin.

I have a job number "060571-1-1" in which I'm trying to extract the sales order from which happens to be the job number without the "0" at the front and the "-1-1" at the end. I would like the sales order column to be just 60571. What formula do I use if the job number cell is sitting in A2 and I want to be able to copy the formula all the way down for different job numbers?


How do I add a letter in front of a column of numbers.
I have a column of stock part numbers and I want to identify them as belonging to a particular supplier by adding a D in front of all the numbers.
There are about a thousand lines, what is the easiest way to do this.

I have 2 columns, 1 with a list of order numbers, and a column next to it with return values (please see attached file).

as you can see the current formula i have is '=IF((FIND("V",I2)),"VO","No")', so this searches for the letter V in the order number column, and if found, returns the value 'VO'. Instead of returning 'No' for false, it simply returns an error instead, #VALUE!.

Obviously i'm doing something wrong but i cannot figure out what. All I want it to do is return "True" if it can find the letter V in the order number column, or "False" if it can't.

Also in the pivot table its returning an option of '10' for that column, I presume thats because it's finding the letter 'V' as the 10th character in the cell, but I only want the column to show true or false.

Would appreciate some help with this,


Hello, does any body know of a function that would insert a letter or a number in front of numbers in a cell for example

column A

I want to insert the prefix "S" in front of the nummbers 3245

so i would hopefully end up with

Column A
Would be great if somebody could point me in the right direction.
Many thnaks

I have a list of with numbers & blanks. For all the numbers, I would like to put the equal sign "=" in front of it.

For if I have 234 in cell, I want it to be =234.

There are several thousands of values in the list.

What I am doing presently is to concatenate a e.g. "X" in front of it then later find & replace "X" with a "=".

Is there a better way to do this?

I want them to be able to copy and paste cell values without it also copying and pasting the validation. This is because I have different validation settings for every single cell, and don't want to change those everytime I copy and paste. I know that you can use paste special to paste values without validations, but that will get tedious, and it doesn't seem to work anyway - when I paste special the value then its as if the paste special overrides whatever validation criteria I was using. Is there a setting or a trick to be able to paste values only all the time without using paste special, AND when I paste values only I dont want it to override the validation I have set? For example, if I have validation set for only allowing numbers between 0 and 2 and input 54 manually an error message occurs of course, but if I copy and paste special the number 54 into the cell it then allows that to happen despite setting validation to only allow numbers between 0 and 2, and despite pasting special values only.

Column A contains numbers. There are a few cells in Column A that have a "x" place in front of the number. Column B shows whether this item is open or closed. I need a formula that will count the number of cells in column A that contain the letter x.

I would then be able to create a pivot table that would show me the number of x'd items that are open and the number that are closed.

1098165-02 closed
x100629-01 open
x100629-02 closed
100104-01 closed

I need to show that there are two in the above list that contain the letter x. One is open, One is closed.

Thanks so much for you help.