I have two columns of data and cannot get the column to change the format to display as currenty or accounting, with a currency £ sign and comma separator...I select it all and go to Format cells to do the changes, but it does nothing, at all!
In cell 'I6' if I enter the text "Balance" I would like it to display as "Actual" but if any other text is entered I would like it to display the text entered. How to do this using custom cell formatting. (Currently on Excel 2010).
I have a large spreadsheet that I would like to indicate the location of the cells (Change cell color or text color) which contain formulas. My thought is that conditional formatting would be the easiest way...
I just upgraded to Excel2010 and noticed that if I calculate percentages resulting from other formulas, the result displays as a percentage with 15 decimal places and it ignores the format of the cell. My guess is that since formulas are being used in cells A1,B1, and C1, Excel is ignoring the format. When I hard code the numbers, I obtain the desired result.
Is there a way to change the default of 15 decimal places for formula results involving inputs that use formulas in their own creation? (outside of the Round function in each cell? Within Options?)
The data looks like this:
A1 = formula resulting in 1.76% B1 = formula resulting in 1.90% C1 = formula resulting in 1.69%
I have a problem that when I try to convert text to number and format the number without 2 decimal places as seen on the link I have given below, Instead of 1607.947, I get 1607947. I have Excel 2010 loaded. The details are in below picture.
I have an Excel 2010 spreadsheet where the default number format keeps switching to the date format. I keep switching it back and it'll stay as a number format for a time, but then it'll switch back. This is also the case for any new worksheets in that one spreadsheet.
This only happens with this one file - everything else behaves according to my regional settings or to how the file had been previously formatted.
In my worksheet I have 'Column A' and 'Column B', In 'Column A' are product I.D. numbers. In 'Column B' is a text description of the product, whose I.D. number is in 'Column A,' and should also contain the I.D. number from 'Column A' somewhere in the midst of the descriptive text. However, some of these in 'Column B' do not.
I need to create a function that looks for the value in 'Column A' and determines whether or not it is present in the text of 'Column B'. Therefore, spitting the answer out in 'Column C' so that I can copy it down for 100,000 cells.
In a field containing this: Bergamot, Orange*(11,16) I want to put a space before the asterisk. There are hundreds of these in the spreadsheet with other text before and after the text to be replaced. I made sure the field is defined as a text field.
I searched on "*(" and said replaced with " *(" and ended up with " *(11,16)" - the Bergamot, Orange was removed from the field.
I'm using Excel 2010). I have column I, which is the sum of columns A and B (first and last names of patrons), and I have used conditional formatting to identify all duplicates in column I with red text. I would now like a macro to find red text in column I and then delete the corresponding rows.
I've been looking around on the web for a while and have cobbled together this code stolen from other people's macros:
When I run it, I get the following error:
Run-time error '1004':Application-defined or object-defined error
Excel doc is called "stupid mailing list.xlsm", sheet I want to run the macro on is called "voters".
Why the heck every time I enter in to the "edit text" area of the chart title box in a chart in Excel 2010 and type "=B27" (without the quotation marks, and understanding the contents of "B27" has the text I wish to display) does this idiot thing simply display "=B27" (again, without the quotation marks)???
I'm trying to use Excel to build a text file based on values from certain cells in Excel 2010. This is based partially off of static text that never changes and variables that will need to change. For example, I want Excel to output a text file that has the following text: The red fox jumped over the $X twice. I would want $X to be replaced by the value of A1 of the active worksheet. I will also have several lines like that, so it won't just be a single line, but anywhere from 20 - 120.
I deal with a rather large excel database (the range is A1:AV168266) that contains customer information. Some of these various customers are affiliated with a group called "ascend" and I need to be able to filter all my list by every customer affilliated with Ascend. The trouble is that the word "ascend" can appear in 8 different columns and usually when it appears in one column it doesn't appear in the others and their can also be more text in the cell beyond the word "ascend". I want to create a formula that will simply look for the word "ascend" in any cell of a row and return a result I can filter by. I will be putting the formula in column AW and put it in all 170,000 rows of that column, that way I can just filter by a single column.
I've played around with combining COUNTIF with SEARCH and MATCH with SEARCH but nothing seems to work.
I have an Excel 2010 spreadsheet that I am using to save several numbers all in the same column. These numbers can range from the several thousands up to billions. The formatting I am using for these numbers is Number (using 1000s separator).
I am also using the spreadsheet to generate a text string for each of the numbers. I don't want the text string to show the number as it is, I want to shorten the number by only showing the first few digits followed by a "B" for billion, "M" for million, or "K" for thousand. For example, in the text string I want to show 1,600,000 as 1.6B.
In order to shorten the number I use the cell in the column to the right of each number. This cell uses the following custom formatting (which I found by doing a Google search): [>999999999.999]#.0,,,"B";[>999999.999]#,,"M";#,"K";
As you can see the formatting is quite complex (at least for me it is) but it does what I want it to do.
Here is a screenshot of what the original and custom formatted cells look like:
As you can see the custom formatting works and does exactly what I wan
The next column (after the custom formatted number) is where I put the generated text string for each of the numbers. As I stated above, I want the generated text to use the shortened version of the number (e.g. 1.6B).
To generate the text I use the CONCATENATE function with a reference to the cell containing the shortened number as one of the arguments. For example:
CONCATENATE("SOME STRING ", B1)
Where B1 is the custom formatted cell.
The problem I am running into is, the text that gets generated doesn't show the shortened format of the number, it shows the full number. Here is a screenshot demonstrating what is happening:
As you can see the generated text is "SOME STRING 1600000000". This is not what I want. I want the generated text to be "SOME STRING 1.6B".
I think I understand what's going on. When the CONCATENATE function references a cell it takes the actual value of the cell and ignores any formatting. (I suppose formatting is just the way you see the data, not how underlying functions receive the data.)
My question is, how can I re-write the CONCATENATE function (or use another function, etc. available to me) to use the formatted version of the cell?
*UPDATE* I have attached my spreadsheet as an attachment to this post (tackyjan_excelforums.xlsx). Please note that it was created and saved using Excel 2010.
Isn't there a checkbox somewhere where I can tell Excel 2007 to leave xls files as they are? My Excel 2010 doesn't urge me to save an old xls file format into xlsx but in Excel 2007 I can't find a switch (and I don't rememeber if I had to do anything in 2010). So ultimately I want to stay in compatibility mode when using old files.
Open a brand new spreadsheet whether the format is set to "General" or "Text" it will not let me enter numbers. For example, when I enter 2007 in the cell it displays 20 07 not really that big of space between the two zeros - but still a space. I can't format this no matter what I do.
I am working with a 2010 Excel spread sheet and need some direction.
One column has dates in it. Some dates are like dd/mm/yyyy (eg 15/03/1974) and others in the same column are in the following format 14th October 1983.
I need all of the dates to be in the first format (dd/mm/yyyy).
Is there a way to do this without manually changing each field? I have already tried highlighting the column, then clicking the 'numbers' arrow and picking 'date' from the number tab but that didn't work. It's never that easy, is it?
Using 2010 - When I paste a chart I can initially retain the format of chart bar colours (Paste Special - source formatting). But when I select a new data source for the chart the formatting of the colours is not retained.
In cell n2 I get: 41526415274156741568481569 This is the excel number for the above dates, but I need it to look like:
Sep. 9 - 10, 2013, Oct. 20 - 22, 2013
I also need it to be able to isolate single dates for example if the date in cell l5 was 10/5/2013 instead of 10/20/2013 then I would want n2 to look like Sep. 9 - 10, 2013, Oct. 5, 2013, Oct. 21 - 22, 2013. but my formula currently only changes that number in the string from 41567 to 41552.
The reason I need this is because this information is part of a identifier and also maybe used in reports, the people reading the reports won't know this long number is actually dates and won't be able to read them if they did.
Also the cells L3 - L7 data depends on cell l2, 1 = one date entered into cell l3, 2 = two dates, one in cell l3, the next in cell l4, and so on for up to five total days that may or may not be consecutive. Also, it seems to work as is, but only for one day.
I have a Column of dates in this format: 5/19/2014 8:08:44 AM I am trying to get this to read 20140519 or yyyymmdd It doesn't matter what format I change to, it will not update unless I activate the cell and then tab or enter out of it. I have 3000 rows to convert. with this either by setting or VB Sub?