Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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.

Example:

X39655
X39656
X39711
39662
39664
39665

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

=Sheet1!A1&"scc"

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

Regards,

Peo Sjoblom




View Answers     

Similar Excel Tutorials

Capitalize First Letter of Every Word in a Cell - PROPER Function
In Excel you can use a function to capitalize the first letter of every word in a cell.  This allows you to prepare ...
Select Cells in Excel using Macros and VBA
This is actually a very easy thing to do and only requires a couple lines of code. Below I will show you how to sel ...
Make Column Headings Numbers instead of Letters - Make R1C1 Style References in Excel 2007
In Microsoft Excel you reference columns as letters by default - A1, B3, C5, etc. But you can also reference the co ...
Keep Leading Zeros in Numbers in Excel - 2 Ways
I'll show you 2 ways to add and keep leading zeros in front of numbers in Excel. These two methods are very simple ...

Helpful Excel Macros

Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
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
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
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
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi

Similar Topics







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

etc...


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


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.
Example:
Column Cell, input
a1, a1
a2,a2
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?
Thanks


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!


Hello,

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


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 ??

Thanks,

Matt.


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!


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
012569528628
027616149237
025192051029
012235111536
012569528734
012569508422
12569508439
86162180934
043396039490
043396603820
012569509023
012569509030
12569512429
12569512436

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.

Next.

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

Next.

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
help.xlsm

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


Hi,

I have a collum with different numbers in each row.

Example:

23
34
56
67
44

I'd like to add the letter P before each number all at once rather then typing
P before each one manually.

Example:
P23
P34
P56
P67
P44

Was wondering to go about doing this?

Thanks

Dino


Hello All,

I have found that excel can be the best thing ever and also a nightmare when you are stuck on something. I really apprecciat anyone who can help me out.

In an excel workbook i have a sheet that has rows and rows of Sales Leads.

In column A i put a letter "C" if they have turned into a client or an "X" if the sale was not made.

I am looking for a function that will look at column A and when it sees the letter "C" then copy the entire row across and put the row into sheet 2.

For example Sheet 1 has

A B C

1 C Client1 etc

2 X Client2 etc

3 C Client3 etc

Sheet 2 should then read:

A B C

1 C Client1 etc
1 C Client3 etc

Sheet 2 should show only client1 and client3 and the information to the right of them because they have a letter "C" in front of them.

The below function is how far i got. Maybe the function can be fixed or maybe you can suggest something new.

=VLOOKUP($A4,Sheet1!$A$2:$M$16000,COLUMN(B:B),0)

The prolem with the above funtion is when i copy it to multiple cells

It returns the inormation of the first customer only. Because the funcion tells it to Find "C" and when it does then it only returns information of Client1 and repeats Client1 over and over. How can i get it to return all of the clients with letter c in front.

Maybe you can help me fix the above funciton or perhaps i have gone down the completely wrong path and you can suggest something new.

Thanks to all who have even read this far and those who reply.

Best,

Atlantic


Hi there,

..I have an annoying problem that crops up from time to time regarding number formatting.

I copy and paste "special" to transfer some calculated numbers into values. However these seems to paste the numbers as "text", and subsequent calculations do not then work.

I have tried highlighting the numbers as "re-formating" them as numbers, but this does not work. I then physically have to re-type each number back into the cell for exdcel to recognise that they are asctually numbers and not text?

Is there a quick way to "copy" or generate a function that will convert "text" numbers to actual "numbers".

Mnay thanks,

Stu


In one worksheet, I have cells with the following formula:
=IF(F$5"",CONCATENATE(F$6, " ",F$5),"")

That formula returns a value such as "ABC 123"

Then I use VB to copy/paste those values to another worksheet, but that command adds an apostrophe in front of the cell value. So the cell still reads ABC 123 but the formula bar shows it to be 'ABC 123.
How can I get it to copy without adding the apostrophe in front? It's causing some problems with my later commands because it puts apostrophes in my otherwise blank cells.

Thank you for any help!!


I'm working at a company whose previous software encoded a number that should be 3 6/12 as 3* 6. I want to turn that into 3.5 (these are example numbers, but the fraction is always 12ths). Excel can't handle the asterisk, so I tried to replace the * with *12+ to create a formula (3*12+6) that would enable me to eventually get the answer I needed, but every time I try to replace the * it deletes the number in front as well, so 3* 6 ends up being *12+6. (The cell format is general.) I also tried using a replace formula in another column. This gave me 3*12+6 but I couldn't figure out how to evaluate it, because it considers itself evaluated already. I tried copying that cell to another and then enabling transition formula entry, but it didn't work either. I know I could just put the 3 in one column and the 6 in another and then create a formula column but I have thousands of data entries and would rather not have to waste all that time.


I have a worksheet with formulas that extract numbers from cells so that I can use VLOOKUP in another worksheet. When I copy and Paste Special to a clean column I keep getting this character ( ' ) in front of the number. Once this happnes the VLOOKUP will not work. Is there something I can do to remove that character besides TRIM or CLEAN, as that just creates another cell with a formula and again VLOOKUP will not recognise the number in those cells.

Thanks,


I have a rather large task that I am sure has an easier way.

I am using Autosum on cells in a COLUMN on sheet-1 to pull values from cells in a ROW on sheet-2. This is a tedious task as the sheet is huge.
The formula remains basically the same throughout with only the row numbers changing. The autosum cells are vertical referencing cells that are horizontal.

For instance,
Sheet-1 A1 would reference " =SUM(Sheet-2!C34)", Sheet-1 A2 would reference " =SUM(Sheet-2!F34)" etc.
Sheet-1 B1 would reference " =SUM(Sheet-2!C63)", Sheet-1 B2 would reference " =SUM(Sheet-2!F63)" etc.

In each case,
all cells in a particular column in sheet-1 references a particular row in sheet-2.
all cells in a particular row in sheet-1 references a particular column in sheet-2. - I hope this all makes sense to you.

Sheet-1 is already built and cannot be changed.

I cannot copy - paste special, transpose because the number of rows/cells differ between the two sheets.
I cannot copy/paste cells from one column to another in sheet-1 because excel , in its wisdom, recalculates the reference.

For instance,
copy Sheet-1 A1:A22 (=SUM(Sheet-2!C34)) and paste to Sheet-1 B1:B22 the formula would change to " =SUM(Sheet-2!H34)" so now I still have to edit H34 to become F63.

I would like to copy the formula exactly as is without it reformating itself. This would keep all of the columns in place and I could then do a replace on the row numbers .
Is there any way to copy formulas from one cell to another without them changing. I tried paste special, paste formulas but that did not work.

Any ideas?
Thanks


Hi. Has anyone every encountered this problem: You receive an Excel spreadsheet that has all the cells formatted as GENERAL. The cells contain numbers, but the format of those number values is not really NUMBER. Therefore, these values fail to calculate in mathematical and lookup formulas. So to fix it, you have to place your cursor in the cell and click ENTER in order to refresh the cell. Then, the format of the value within the cell converts to NUMBER, and your formulas start to work.

Please see my attached spreadsheet. Is there a macro that can convert the values in column A to DATE, and the values in column B to NUMBER?

I know of a trick using the COPY PASTE SPECIAL VALUES - MULTIPLY by 1.0 method, but I was wondering if the process could be more automated with a macro.

Any advise is greatly appreciated.


Hi there,

I'm trying to create a formula to get Excel 2003 to do basically this:

if the value of column i = 0 to 5 the letter value shown is L
if the value of column i - 6 to 11 the letter value shown is M
if the value of column i - 12 to 17 the letter value shown is H

basically i have columns F, G, H that have numerical values and a formula that adds those together to come up with a number 0 to 17. I want that number, based on the idea above, to visualize a letter instead of the number

for example:

F = 4
G = 4
H = 7
I = 15 so it would show up as H in the cell.

something like this:
=SUM(F12:H12) ----- THEN add the other part where if the answer is 0-5 it displays L, etc.

Thanks for any help and input!

Stephanie


Hi,
I have a column of values that are returned by a formula, which is derived by referencing a few cells in my spreadsheet. I want to copy the entire column of values and paste it into an adjacent column. However, when I do this the cells that are referenced in the formula shift one column over. I can't do find/replace because each row is a different number, and it doesn't work if I try to replace the column letter with another letter. Does anyone have any suggestions? Thanks!


Dear all,

I would be very grateful if anyone could help me with the following problem:

I have one column: in the first cell comes text (beginning either with the letter "L" or "R") and afterwards, in the next cell, comes a number (either "7" or "8"). Basically the column is made up of alternating cells containing either text (code of a movie) or numbers (responses to the respective movies). I want to find a formula which writes either:

- "correct" if the number "7" follows a cell containing the letter "L" or an "8" follows a cell containing the letter "R";

or

- "incorrect" if the number "7" follows a cell starting with the letter "R" or the number "8" follows a cell starting with the letter "L".

Basically the 8 is always correct with an R and the 7 with an L:

movie R01
response 8 ---> this would be correct
movie R02
response 7 ---> this would be incorrect
movie L01
response 8 ---> this would be incorrect
movie L02
response 7 ---> this would be correct

etc.


I have tried using IF(ISNUMBER(SEARCH...but somehow, I just don't manage to make a useful formula out of it...


I would very much appreciate any ideas! Thank you very much in advance!

Alex


I am running a report with mobile numbers. and it gives me the numbers ( 7197757123) however i need to add dialling code 44 in front of these . as there are 20,000 of these i need a forumla to help.

so place 44 in A1 and the mobile number in b1, then i need a forumula for c1 to place a1 in front of b1

any help would be appreciated

thanks