# Insert A Letter Or A Number In Front Of Numbers In A Cell

Oct 3, 2008
Need a function that would insert a letter or a number in front of numbers in a cell for example

column A

3245

I want to insert the prefix "S" in front of the nummbers 3245. so i would hopefully end up with

Column A

S3245

View 3 Replies
ADVERTISEMENT
Sep 24, 2008

I can't seem to make user-defined format that puts a text in front of a number and/or a text.

Let's say I have A1: 13, A2: texttext A3: text7 and I want to format a lot of cells to "Ilike 13" / "Ilike texttext" / "Ilike text7"... ie add the same text in the front of the cell, no matter what the content is.

I did manage it seperately, with "texttext" @ for text and "texttext" # for numbers, but what's the general one?

View 12 Replies
View Related
May 5, 2007

I have a column of data that is given to me that is a mix of letters and numbers and I need the numbers to have leading zeros, they must all be three digits. The data has either 3, 4, or 5 letters followed by numbers 1 through 999. Example: ABCD7 I need to change it to ABCD007. I am using Excel 2004 for the Mac.

View 9 Replies
View Related
Jun 19, 2006

I need to add an extra four zeros to a number in a cell - in this case an ID number, so that i can do a lookup from another list. Basically what i have is two lists of ID numbers in a field of a database, in one i have the correct display/format, so that a number would look like 000054454545. In the second list however the number is only shown as 54454545, due to differences in the programs which imported them. I would like to know if its possible to use a function or macro in excel to basically insert the four zeros onto the number ie 0000 + 54454545 = 000054454545 so that i can do a lookup of one for the other.

View 4 Replies
View Related
May 10, 2014

I have many lines of text and I wondered if there is a formula so I can insert a comma before the first capital letter of each line? A small amount of text is below

leave on left Salter Road

right Brunel Road

What i would like is there to be a comma before the first capital letter so it reads

leave on left, Salter Road

right, Brunel Road

Is this even possible?

View 14 Replies
View Related
Jul 11, 2009

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.

View 14 Replies
View Related
Jul 30, 2008

I'am trying to add a row of cell that contain a letter befor the number

I need to add-up the numbers and ignore the letter

View 12 Replies
View Related
Nov 6, 2008

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.

View 2 Replies
View Related
Feb 18, 2012

Is there a formula for adding zero's in front of numbers?

Example:

If a single number is found in cell B1 add two zeros (2 would become 002)

If two numbers found in same cell B1 add one zero (34 would become 034)

View 7 Replies
View Related
Oct 14, 2008

I have a list of names and I need to know how many names are greater than 6 characters in length. What is the formula I need to enter?

View 9 Replies
View Related
Aug 15, 2007

I need to know how I can delete NUMBERS in front of the names....

I.E.

Colume B

12Smith

12John

13Chris

152Matt

1111Joe

12569Joe

1234Smith

I need to delete the numbers in front of the names - i have about 26thousand records like this and need to know how i can delete them.

View 9 Replies
View Related
Oct 4, 2012

I am trying to make a formula which will tell me if A1 is a postcode (a letter and a number e.g CV42 6AQ)

In the A column, it looks like this:

CV42 6AQ

FC45 D4D

West yorkshire

PR42 6RD

Etc.

i want it to identify all the postcodes, and NOT "West Yorkshire" because it does not contain a number.

View 3 Replies
View Related
Jan 27, 2006

I WANT TO INSERT A LETTER IN FRONT OF A NUMBERS THAT ALREADY EXIST IN CELLS

IN A COLUMN. SORT OF LIKE USING "FIND & REPLACE" EXCEPT THAT I DON'T HAVE

ANYTHING TO REPLACE; I JUST WANT TO INSERT A LETTER PREFIX IN FRONT OF

NUMBERS.

View 9 Replies
View Related
Dec 3, 2009

I want the A4 cell contains the calculation of B4 (but the number gained from the funtion row and if the B1 cell contains the number 10 the K(B1)=K10

[A4]=B(row())*K(B1)

View 4 Replies
View Related
Feb 21, 2014

I have a macro already that prints my blank invoices sequentially. What I would now like to do, is insert a dash. SO instead of just the invoice number, I would like to have a 1- in front of each number;

1-1

1-2

1-3

1-4 ... etc.

I have uploaded the 'invoice' that I am currently using.

View 6 Replies
View Related
Mar 19, 2009

i want to know how to prefix a minus sign (-) before numbers in cells in a large range.i m working on a large sheet containing the Numbers with Cr and Dr as suffixes just like 445Dr ... 3331Cr and so..on... in the worksheet

i want to know the method of deleting the suffixes and prefixing - sign infront of numbers having Cr as the suffix.

Numbers with Dr as suffix denote positive numbers

and numbers with Cr suffix denote negative numbers. i want to prefix the -minus sign in front of numbers having Cr in the end.

View 8 Replies
View Related
Jun 30, 2006

I have a question, how do i display a number 0 in front of another number? Example, I am working with these zipcodes and there is a 000213 but it only shows up as 213 in the cell. Is there a way for it to be 000213 with out me using the tilde sign `000213?

View 4 Replies
View Related
May 31, 2014

i have this in my 1 cell: ttgtcctacttacaacactgtgcttagtaatggttattgcgactttatccttgttctgaa i want to count how many "a" in this cell . which formula i can use to solve this problem ?

View 8 Replies
View Related
Sep 8, 2008

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.

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.

View 9 Replies
View Related
Dec 30, 2009

i want to fill down a column and instead of my formula changing from A6 to A7 i want it to change to B6.

View 9 Replies
View Related
Jan 21, 2012

Using the following data

R10-12128

R11-12x12x8

R11-12x12x8

R1-12x12x8

R1-12x12x8

R12-12x12x8

R14-12x12x8

R16-12x12x8

R18-12x12x8

R2-12x12x8

I want it to sort like this:

R1-12x12x8

R1-12x12x8

R2-12x12x8

R10-12128

R11-12x12x8

R11-12x12x8

R12-12x12x8

R14-12x12x8

R16-12x12x8

R18-12x12x8

What is the formula to achieve this?

View 5 Replies
View Related
Apr 3, 2014

I'm trying to write an IF formula that will return a number if the word in the adjacent cell begins with a specific letter. Here's what I want to show:

City

01

Express

02

Overnight

03

So "C" would return 01, "E" would return 02 and "O" would return 03.

View 3 Replies
View Related
Jun 20, 2012

reading a zero number in a cell

here an example :

on Cell A1 = 01

on Cell B2 = 10

so I write at C1 the formula is =left(A1,1) but how come the number that comes out is 1 not 0 (zero) ?

but if I write at D1 the formula is = right(A1,1) the number comes out is 0 (zero)

how to make the formula that can read the zero in Cell C1?

View 9 Replies
View Related
Jun 6, 2014

I have a list of numbers with 2, 3, and 4 digits. Is there a formula that can recognize if there are only 2 digits, it will add 2 zeros in front of the number.

For example,

47 will become 0047

234 will become 0234

1234 will become 1234

View 4 Replies
View Related
Apr 14, 2009

I need to know how to remove characters from the date field in Excel.

Example:

9/12/1975 - I need it to read 09121975.

I also need to know how to remove characters from a social security number without removing the zero's in the front.

Example:

012-34-5678 - I need 012345678.

View 9 Replies
View Related
Dec 31, 2009

For the below formula is it possible to replace the B's (column location) with a cell Say Z146 which contains the letter B (or a number if thats easier and someone can tell me the numbers for each column).

When the formula is dragged into the next cell (down) it takes its column reference from Z147 and then my life becomes so much easier.

=IF(INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0))"",INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0)),"")

View 9 Replies
View Related
May 20, 2006

Why is Excel so back-***wards on this? Is there a VBA solution to having a new sheet inserted after, not before, the current sheet that can be attached to an icon?

View 3 Replies
View Related
May 2, 2007

I need this for a tracking sheet of scores. For example, 1 gets 100 points, 2 gets 90 points, 3 gets 80 points, etc. I need to set it up for 10 places. I have no idea and have fiddled with it for two hours now. I need to be able to put a 1 in the cell and 100 appears after I hit enter, etc.

View 5 Replies
View Related
Jun 11, 2008

I'm working on some code that's part of a userform. To illustrate what I need, I will give an example. A column letter, 'J' for example, is stored in colNum.Value taken from the userform. I need both a column inserted before column J, and data entered into that new column in row 2 (thus J2, which would now be blank).

View 4 Replies
View Related
Nov 5, 2013

I would like to change a number to a letter and then drop a digit from the end.

Say my data in A1 reads 81234568, and I would like it to display in cell A2 as h123456.

View 3 Replies
View Related
Dec 21, 2012

I have letter and number combination code in two collumns and they differ for 10.000 numbers:

BAM98314

BAM88314

BAM90000

BAM80000

As you can see the left code is for 10000 numbers higher. the letters are allways the same. In the event that this isn't so, if difference between codes in same row is more or less than 10000 numbers. I was thinking on making conditional formating so the cells with wrong difference would be marked red, but I do not know how to make formula for this difference.

View 2 Replies
View Related
Feb 3, 2014

How to translate a specific number into a specific letter related to the number itself.

I have lots of different numbers for example all going down a specific cell (These numbers I want to separate into 3 different catagories. The positive numbers I want to be calculated as the same Letter that is next to that row directly over to the left of that cell.

So if the letter

B / cell / cell / +9 / Then I want if its a positive number like +9 to answer in the formula with the same letter (B) listed to the right in the same row in a different cell of course

B / cell / cell / -6 / Then I want if its any negative number like -6 to answer in the formula as the opposite Letter of (P) listed to the right in the same row in a different cell of course

P / cell / cell / +17 / Then I want if its a positive number like +17 to answer in the formula as the same letter (P) listed to the right in the same row in a different cell of course

P / cell / cell / -3 / Then I want if its a negative number like -3 to answer in the formula as the opposite Letter of (B) listed to the right in the same row in a different cell of course

and thirdly if its a (0) zero and only (zero's) Then I want the answer to stay (0) zero.

The number can be any numbers it doesn't matter. Only distinction is that positive number of +1 and up repeat and stay the same letter that the cell on the far left is already stated.

and all the negative number of -1 amd below all reverse the letter to the opposite letter of (B to a P) or (P to a B) listed to the right in the same row in a different cell of course

and of course (0) zero would be listed as a positive number and only return a (0) zero as the result of the formula.

View 9 Replies
View Related
Nov 15, 2009

to create a macro that insert [url] for column a infront of the data currently there.

So in coulumn A, it might have default.asp

I wish to insert [url] infront of it for column A from range row 1 to 10.

View 11 Replies
View Related
Oct 11, 2008

I have a Col where the cells have been formatted to enter "PR" before the numbers input into any cell in that Col. I did this by using the custom selection in the format cell. However some times i need to input the number with the letter "A" at the end. For instance PR123456A.

Now in some cells it lets me do this but in others it does not. It would end up with 123456A, loosing the PR at the front. I have checked that all is well within the custom window. Can any body offer an explanation as to why it could be doing this.

View 2 Replies
View Related
Apr 7, 2014

In sheet1 I have a simple database consisting of 5 columns of data

Column A : Name ie James Jones

Column B : payroll number ie 123456

Column C : shift times ie 1245-2124

Column D : job title ie floor

Column E : comments ie A/L or 0600-1500

what I would like is some code that will go down Column E and if a 'time string' ie 1300-2130 is found then copy this string and paste into corresponding value in column C. If a text string is found ie A/L or Sick or anything like this then ignore and move onto next cell, loop this until all cells in column E have been checked.

View 4 Replies
View Related
Feb 9, 2014

I am looking to be able to compare a number with a letter so for example 4c and compare it with another 5c and in that scenario see a difference of 1 but when the two to be compared are 4c and 4a it would see 0.6.

in this excersize

A=0.33333R

B=0.33333R

C=0.33333R

it is basically sub category of the initial number so an A is better than a B and in tern a B is better than a C

for example

Targeted Working At Residual

4c 4b 0.33333r

5a 4c -0.33333r

4c 5c 1

4c 5a 0.33333r

5c 3b -1.66666

so on and such forth the number and letter can be entered into seperate fields the lettering are only A,B,C but numbers can range from 1-9 i am normally quite ept with excel but this is out of my depth

View 4 Replies
View Related
May 21, 2009

On column "F" I have all the names listed, they are all lowercase, and with different length. How do I write a formula to turn those names into numbers?

For an example. In column F1 I have "James". And using letters = numbers

9 = A L T

8 = B N W

7 = E P X

6 = G R Y

5 = I J

4 = H S

3 = F Q Z

2 = D O U

1 = C M V

I would like the column G1 to display "59174"?

Would need the convention from letters to numbers on Column G adjacent with the name.

View 9 Replies
View Related
Feb 7, 2008

I found lots of threads for inserting rows but not the one I need. I'm trying to insert rows with a macro on the position as specified in a cell f.e. in cell D4 I type the number 80 then the macro has to insert three rows at row 80

View 2 Replies
View Related
Aug 21, 2009

I need to extract (and then use for SumIfs) only item numbers from the long description. Please see the attached list where item number column shows existing list & next column shows what i want to extract. The exrtacted part if has any trailing or succeeding letters, characters between numbers should stay. for example from "SGA:RV-SVA:PEPPERS/PEPPERONCINI:SV9176001/232034" I need to extract " SV9176001/232034" or from " SPICES:BULK SPICES 7100:9054B" I need to extract " 7100:9054B". Can some one please urgently help me on this.

View 9 Replies
View Related
Jan 25, 2009

how it would be possible to obtain the last two numbers before the letter B, if "Fri Aug 01 16:40:49 BST 2008" is placed in A1 so "49" would be placed in B1,

View 9 Replies
View Related