Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
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

Convert Column Number to Letter Using a Formula in Excel
How to get a column letter from a number in Excel using a simple formula. This is an important thing to be able to ...
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 ...
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 ...
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 ...
Convert Scientific Notation to Numbers in Excel
How to convert scientific numbers to show their full amount; this method also allows you to retain the appearance o ...

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







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!


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.


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


I have an exported CSV file that needs to be uploaded into another system for a 911 database. I have over 4000 entries and in one column, I have the extension numbers of my users which is a 4 digit number. I need to place my 3 digit prefix in front of these 4 digit numbers for the database to work correctly. The 3 digit prefix is the same number for all cells. The merge cell doesn't work. I need to do this on a weekly basis, so hopefully there is a simple formula or feature that would work.

Thanks for any input you may have.




Hello everyone,

I'm a little confused this morning. Here is the scenario:

Column A is formatted as text because I need trailing zero's to show up because I am dealing with part numbers. There is no apostrophe present in any cell in this column.
Column B has the formula ="'"&A2 because I need a single apostrophe in front of this number to paste in "general" format for vlookups.
Column C I use to paste the entire Column B as a "value" (formatted general), but instead I am given two apostrophes instead of one preceding the number.

Hopefully an easy fix. Any thoughts?

Hi,

I had a quick look through some of the previous post and couldn't find a solution to my problem.

I want to apply Data Validation to a cell, so that only the following combination of letters and numbers can be entered.

Letter Letter Number Number Number Number Number Number Letter.
e.g AB123456C.

Any help would be greatly appreciated,
Thanks,
Dave


Hi

Could any one please help me adding apostrophe (') in front of the data with number format only?

I have a list of part numbers that I am trying to import to Access. But after importing the records with format "number" appear as "#Num!". But if I add apostrophe in front of the records then the record appear fine in Access. But the apostrophe should only be in front of the numbers only not in front of text.

I am new to writing codes in VBA so can't separate numbers from text for adding apostrophe. Any help is appreciated.

Thanks
Rajesh


I'm trying to get cells containing numbers and text to align in the column. This would be easy to solve with custom number formatting if I was only displaying numbers. For example, cell A1 contains the number 2 and cell A2 contains the number 56. Both cells are left-justified. With general number formatting, the number 2 in A1 aligns with the number 5 in A2. If I format the cells with a custom number format of "???" then the number 2 in A1 aligns with the number 6 in A2. In other words, the "ones", "tens", "hundreds" places in the column line up. However as soon as I add text after the number, the formatting reverts to normal, i.e. I no longer get the space in front of single-digit numbers. I tried using custom number format "???;;;@" but that didn't help. The only way I can get the numbers to align is to pad single digits with a leading "0". Is there a way to do what I'm trying to do? I have attached a .jpg file illustrating the problem if the above is not clear.


Why do 16 digit numbers convert? Is there a way to format the cell rather than adding the ' in front of the number to stop the conversion?
This number 4.50542E+15 should look like 4505420254200425, but when I add the ' in front of the number the last digit changes to zero 4505420254200420. Every time we miss this, it costs us money.


Hi,

I am copying cell values from one column to another using VBA. I can do the copy/paste fine I just need to determine if what I am pasting contains a letter so I can determine what column to copy to. The cells all contain numbers so I will only need to seperate numbers combined with letters.

I need to do the opposite for another column but I need to determine if cells containing letters have any numbers and place them in the proper column.

Any ideas?

Thanks,
Brian


I can copy and paste my debit card purchaes from my bank - online.

The numbers are in a column and appear as $14.34 (ex.)- with a dollar sign
in front of them.

I can't sum these numbers. I select them all and convert them to a number
format and they still won't sum.

I f2 the cell and remove the dollar sign and they still won't sum (sum = 0)

Any ideas?





Experts:

I have a file in which on one of the tabs an equal sign is placed in front if numbers are entered. For instance, if I type "1/1/07" in the cell, instead of a date being entered, excel is putting an equal sign out front so it actually divides 1/1/07 giving a real small number.

There are other worksheets in this workbook, but they don't have this issue. I can't figure out what is going on. Any help would be much appreciated. Thanks.


Is it possible to subtract a number from a column letter?
I'm trying to create a sheet that does some helpful calculations. In order for me to finish it without a macro i need to be able to subtract a number in a cell from a column letter.
Example F-5=A, The end i'm trying to achieve is to get a sum range to move left and right in this data set based on that number.

Any ideas?

Thanks All,

Chris


Hi,
I am printing an excel document, and I set first 4 rows to repeat at top from page setup menu.
I also "Page 1 of ?" in the header from header/footer tab on page setup

Problem: Page numbers are shown on back of the cells that are repeated

I want the page numbers to show in front of the repeated cells.

Tried: - changing margins, but page numbers always go on back of the cells
- drawing->bring to front or back doesn;t work

As always, any help is appreciated


Hi folks

I am fiarly new to discussin boards and have a general question relating to Excel 07 please.

I wish to determine the number of words in a column that begin with a particular letter.


For example - Assume Cell A1 has the word DOG, A2 has CAT, A3 has CAR, A4 has TAR and A5 has PIG. Is there a formula that i can place into A6 that sums the number of words in the column from A1 to A5 that begin with the letter C. In this example the answer "2" would appear in A6 as there are 2 words in the list that commence with the letter "C".

Any feedback is greatly appreciated

Cheers

Jim


Hi, I have this sheet this morning that has lots of numbers, alinged in center with error beside them that says "Number Stored as Text". At first I just tried format the whole column as a number, but that changes nothing . Can someone explain why that does not change anything. I thought it would get rid of all the Error codes but it does nothing. I don't want to turn off the Error code as I need to see what numbers were formatted like this.

I am guessing someone cut and pasted these numbers in, and maybe pasted as TEXT or Unicode Text, right now I am clicking on small groups of them and converting them to a number.

I also tried high lighting the whole SS and removing all formats (that removes everything except this TEXT tthing) , I need to reformat and rebuild this whole sheet anyway. But when I do that it still does not convert all these numbers that are formated as text back to numbers. I have a 1000 lines or so is there a faster way to convert this, if I paste this whole sheet into a new sheet and use Paste Special-values would that then paste the TEXT back in as numbers?

Thanks
Ken


Hi,

Is there a formula or vb code i can use to find a numbers after a specific letter in a cell no matter where that letter is in the cell?

Example: G0Z-1.4743Y1.2479X2..... I would like to find the 1.2479 after the "y" and use it in a formula. My goal is to find that number, divide it by .0076358155 and replace the existing number with the answer. Is this possible?

Thanks,

Kevin


Hi,

Does anyone know the VBA code for a macro in excel to record the motion of finding and then substituting values from individual cells from one column for the individual cells in another column?

For example:

Cells A2 to A48 in workbook1 contains a list of many different values (Peugeot, Alfa Romeo, Ford, Renault etc...) and in cells B2 to B48 are their corresponding decimal values that need to replace them in another workbook (workbook2).

In Column A of workbook2 has thousands of instances of the values present in A2 to A48 of workbook1. The numeric values from B2 to B48 (lets say ford = 0.1 and peugeot = -0.4) need to replace the original text data in column A of workbook 2. (so 0.1 replaces ford and -0.4 replaces peugeot)

I've tried using macros but it will only run the exact steps it recorded and not move down the cells even when using relative references.

Because there are 2000 rows of data, using copy (from A2) and paste (into find from find and replace), and then copy( from B2) and paste into replace (from find and replace) is proving very tedious and time consuming as there are 24 columns to do this for (not forgetting the 2000 rows!).

Please Help!!!

Thanks


I'm seeking to analyze data copied from stock broker on-line statement.
The values copy across as text with (what appears to be) a space
before and after each number. I can manually delete the spaces by
pressing F2 and the delete or backspace key as appropriate for the space
at the front and rear of the number which is otherwise of the form
-$123,456.00

The function Value() does not retrieve the value from the text stream.

The function Trim() does not remove the spaces.

The menu function, "replace" does not find the spaces when you use the
space bar to enter the item to be searched for.

Is there a special character that appears as a space but is not a space?



I need to review a list of approximately 5000 cells. The cell contains either a number format something like .2345 or a number with a slash .071/.068

What I want to do seems simple but I don't know how to do it effectively:

If the cell does not contain a /, then I want to put the cell value into two cells d2 and e2.
If the number does contain a / , then I want to put the number to the left of the slash into the d2 cell and the number to the right of the slash into the e2 cell.

I have tried to do a select case statement to have it step through each scenario, but I cannot figure out how to find the / in the first case. After the slash is found, I am guessing the vba script should be similar to excel: LEFT($C2,FIND("/",$C2,1)-1) where C2 contains the original string.

I also have cases for when the cell contains "GA". Those are more complex because I have to step through what number is in front of the GA. Not difficult but definitely time consuming.

Thanks in advance for your help.


I use a USB barcode scanner which reads data in a barcode label and enters it into a cell in Excel. The barcodes all contain seven characters.

The barcode lablels all have the following format: A318639....that is a letter followed by six numbers.

When I scan the barcodes into any cell, a dollar sign "$" appears in the front of the barcode string that is entered into the cell. The dollar sign is not a relevant part of the string of characters in the barcode label.

What I am looking for is a solution where upon scanning a barcode into any cell, the dollar sign is removed from the front of my string and I am left with just the seven character string which is shown above.

I have experimented with the Data Validation tool by trying to limit the number of characters that are allowed in a cell to seven characters, but that has not actually removed any characters from my data string.

Any ideas? Thanks in advance for your wisdom.

Justin M.


Every month, I download several sets of data from an internet site. The only way I can do this is to copy and paste into Excel. The problem is, that all of the cells with numbers in them show up as text, and when I press F2, I find that there is a blank space at the end of each number.

Find and replace, searching for a blank space does not work - Excel "cannot find any data to replace". That function does work on cells in which I have created my own space at the end of a number, but not on these numbers. That leads me to believe that that "blank space" is actually some other invisible character that I need to be searching for. I've attached a bit of my data - maybe somebody else can identify my phantom character.

To get around this, I have been using the formula =(left(A1,len(A1)-1), and that works, it's just that it is a pain to create another table of 20-some columns with this formula, then copy those to values, then for each column convert text to columns to change the text to values, then search and replace for #value! where the original cells were blank.

I'm not into VB. A different solution would be appreciated.

Thanks


Hello

I have a cell A1 that is updated every 5 seconds from data I receive from a DDE server (those data are just numbers between 0 and 10)

What I need is to store every minute the number from A1 in order to keep historical data of those numbers.

So let say at 8:00, A1 number is copied in C2 cell and kept, at 8:01 A1 number is copied in C3 cell and kept, at 8:02 A1 number is copied in C4 cell and kept...
so basically I need 2 or 3 column
column A is just for A1 values that changes every 5 seconds or so
column B is the time with an increment of 1mn
column C is the value of A1 at the time in B like a snapshot at a specific time....

are there any formula or command or macro to do that, and if yes, how?

Hope you can help me...


I have a column of numbers that were imported from a web site and they won't
sort correctly. I have tried reformatting the cells as general numbers but
that doesn't change anything. I discovered that numbers below 100 have a
space or invisible data in front of the number. If I remove it, the number
then shifts to right alignment in the cell and will sort as a number. Is
there any way I can remove these spaces from all numbers at once instead of
going down the column row by row?



I have a field in my Access query that is called RequisitionNbr. The numeric data in this field is not consistent as far as the digits. There are some cells with 1 digit number, 2 digit numbers 3 digit numbers, 4 digit numbers etc. I want to add zeros in front of all cells in that field with < 4 digits. Example if this field has 1 digit number like 3, I want the result to be 0003. If it has 2 digits like 24, I want the result to be 0024. If it has a 3 digit number like 424, I want the result to be 0424. Anything 4 digit and above should just be left alone. How can I put this in a query in access. Thanks for your help