I have an excel spreadsheet, that is 11,000 lines (give or take) and in column A, I have a sequence of numbers on every line set up like this:
11-22-333-44W5
What I want to do is add the number "100/" to the begining of every number sequence. If I have to manually add it, I have to go down 11,000 Lines and add it to every line, is there a way to just tell excel to add the number "100/" and down fill it for everyline - end result will look like:
I wrote an excel program in Excel'03 for a dental office to manage state assistance patients and one of the table columns is the state assistance number. The problem is that the program defaults to a number format when it is entered instead of a text format, which is what I want. The issue is that state assistance numbers are always 8 digits and when it defaults to a number field the program drops the required preceeding zeros.
For example the number 00123456 will sometimes show up as 123456, which is wrong. It's like sometimes it will show up right and other times it gets a butterfly in its brain and deletes those zeros irritating the receptionist here. So, how do I get the table to either stay in text format or set up a number format that keeps the preceeding zeros?
I having a bit of trouble entering numbers in Excel, every time I try to enter a number it seems to divide it by 100. For example : - 1 entered in to a cell becomes 0.01, 100 becomes 1....etc. I've tried formatting the cells and had no luck, tried Tool>Options but not sure where to look.
Is it possible to assign a number format to a cell based on a different cell's value? For example, say cell value of A1 is "BB-". Can I make it where if I enter a "2" in cell A2 it will show "BB-2"?
I have two columns of cells that need to be reformatted: B and C. In the B column excel is reading the cells as time (0:41:55.0), I need it to display those cells as a number like this: 00 41 55.0. In column C the cells look like this: 41 : 09 : 47.3. I need the C column cells to look just like the formatted B column cells (41 09 47.3) Also, even though they have the spaces, the numbers should be read by excel as one whole number (i.e. 41 09 47.3 would be used in a calculation as 410947.3)
I have a dropdown box to select sizes. e.g. 0929 0930 0931 09.2529 09.2530 09.2531 09.529 09.530 09.531
My issue is if I format the number to accept 4 decimal places the 09.529 number becomes 09.5290. I don't know what I can do to fix this as some need 4 decimal places and others will require none.
Is there a way to format a number within a sentence? i.e.
In cell A1 I have 2,000 entered and formatted as a number. In cell A2 I type in ="How can I format "&A1&" ?"
When I do this, the result in A2 is How can I format 2000?
Is there a way to format the 2000 to show the thousand separator comma? If the number were -2,000 instead, is there a way to show both the comma and parentheses around the number? (without putting the parentheses manually into the formula)?
I am trying to figure out why my number will not format correctly... I have a number thats value is pulled in via external data that gets updated everyday.
Everything works fine there, but I needed the format of that number that was pulled in to have parenthesis around it...
I know that one can add the parenthesis to the number by using the ="("&(Number)&")" function...
What the problem is is that when I add the parenthesis to the number it will not allow me to view it as a percentage; it will leave it as a full general number (Ex. (0.0006), instead of (0.06%)
Is there a way to get the percentage number format back?
Is there any way to turn a number that is in general/text format into a date?
IE:
82005 92005 102005
to read:
8/2005 or Aug'05 9/2005 or Sep'05 10/2005 or Oct'05
I have tried using IF statements and it looks like there's a limit in the amount of scenerios you can have. I get data in text format that comes to me in numbers and I'm just looking to turn those numbers into dates so I can sort them properly.
Sub CommentThem() Dim cell As Range On Error Resume Next Selection.ClearComments On Error GoTo 0 For Each cell In Intersect(Selection, ActiveSheet.UsedRange) If cell.Formula "" Then cell.AddComment cell.Comment.Visible = False On Error Resume Next 'fails on invalid formula cell.Comment.Text Text:=" Days: " & cell.Value & Chr(10) On Error GoTo 0 End If Next cell End Sub
I am using this formula to extract the [h]:mm value from a cell and place it into the cell comments. The current result gives me a result of Days: 3.9339797008547 for a cell with the value of 94:24. Is there anyway to change this to only 2 decimal places (3.93) without changing the initial cell value.
I have two columns, one is with number and one is with text, what i have to do concate both columns data in one column with formatting. but when i m adding with & operator or by concatenate, formatting disappear.
I have a few rows in an excel sheet and I want to highlight the highest number(s) in each row.
I've been playing around with conditional formatting, and using the formula: =$C23=MAX($C$23:$C$55) in the conditional formatting box and selecting a formatting type, I can automatically highlight the highest number in column C (row 23 to 55). However, this formula only seems to work when I'm selecting columns of data rather than rows. If I want to highlight the highest number in row 23, the formula I'm using is: =$C23=MAX($C$23:$M$23) Which I think should highlight the highest number From C23 to M23, but it doesn't - nothing happens.
I have an worksheet where I use sequential numbers in column B but it often happens that we insert some more rows with the same number in the B column.
Ex.:
01 02 03 04 04 04 04 05 06 06
Now we would like to have a macro that will format the text color (change to white) in columns B & D as soon as there is a second (or more) occurrence(s) of the same preceding number.
Ex.:
01 – Black (unchanged) 02 – Black (unchanged) 03 – Black (unchanged) 04 – Black (unchanged) 04 – White (changed) 04 – White (changed) 04 – White (changed) 05 – Black (unchanged) 06 – Black (unchanged) 06 – White (changed)
I would to show the number like this if it hasn't decimal like 2 showing in 2 not 2.0 and if it has decimal like 1.5 show 1.5, 1.75 show 1.75.... 1 1.5 0.5 2 1.75 0.25 Now I try this "_(* #,##0.0_);_(* (#,##0);_(* "-"??_);_(@_)"
I need a custom number format to do the following: When a whole number, shows only whole number (no decimal place) When decimal, show decimal. For example, have this data show as typed:
45 (not 45.0) 32 6.7 43.5 87
I can't figure out a format to allow this. The closest I get is: ??.? but that gives me: 45. 32. 6.7 43.5 87.
I need to reformat phone numbers in Excel, and I am having trouble doing so. I do not have VBA experience, but I am somewhat familiar with functions in Excel. I need to go from 555-555-5555 to (555) 555-5555
Whenever I make one with numbers, I get formatting for a number as below
1234567.78(no commas and 2 decimal places)
I normally want the formatting as 1,234,568 (comma inserted, no decimals,), (the last digit is changed just becasue of round off, other wise number in both cases is same).
Now I can double click the field, goto number--> number and then apply this formatting.
My question is whether there is a setting in excel somewhere so that this formatting will come as default (after I create the pivot table each time)?
I have a row of data I want to be blacked out if there isnt a reference number in the first column
So if I type the reference number i can in theory then use the rest of the row but if there is no reference number in the first column i cant see any of the row ...
I currently have account numbers formatted 1.234.5678 and need to convert them all to 1234.5678. how this can be done without manually keying 22,000 account numbers?
Problem : Belgium has three types of phone numbers
##/###.##.## (if prefix is "02" or "03") ####/##.##.## (if prefix is "04##") ###/##.##.## (everything else)
depending on the prefix.
How can I put a conditional number formatting on a cell, so that it puts the input (eg. 025648595) in the correct format (in this case : 02/564.85.95, or something along these lines)?
I have a mapping program that uploads data with the date and time but when it exports it the time is converted to just a number string, e.g. 15:12:37 becomes 151237000. Is there way to eliminate the last three zeros and insert a ":" between the second and third, and fourth and fifth characters?
I have an issue where I need to apply certain formats to a number of text strings which are domain names. This is purely for reporting purposes. I don't need to actually visit the url. This video demonstrates a bit more. [URL]
i am trying to change the number format of one cell from say "general" to hh:mm:ss depending on the number format of another cell using a macro...
heres what i have so far.
Sub cellFormat() If Range("F4").NumberFormat = "hh:mm:ss" Then Range("F2").NumberFormat = "hh:mm:ss" Else Range("F2").NumberFormat = "General" End If End Sub
but its not working, can anyone see the error of my way?
