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