Let's say that I want cell B1 to autocopy cell A1, I will use =A1. However, let's say that the text in A1 goes as follows "text in bold and text not in bold" , what formula must I use so that it reproduces the exact same text formatting?
View 1 RepliesI have prapared a formula for B2,B3 and C2,C3 and the reult came in B10,B11,C10,C11 and D10,D11,E10,E11.
From that relust i want to get the final result should be as follows.
B10 should again divided in to anil(in one cell) and 16/12/2013 11:33:50 (in another cell). This formula to be added in B10,B11,C10,C11 and D10,D11,E10,E11 formula.
I loooking for the final reslt, i have explain the in the excel. I reuired the final result with single formula from B2,B3 and C2,C3.
I am trying to set some conditional formatting, but for some reason on of the conditions is not working and I was wondering if anybody know how -
View 3 Replies View RelatedI am trying to generate a formula for formatting text that will remove all spaces, comma's etc. Examples as follows:
The Bank Job -> the _bank_job_small.gif
Picture This! -> picture_this_small.gif
War Games: The Dead Code -> war_games_the_dead_code_small.gif
Chacun son combat (Never Back Down) -> chacun_son_combat_small.gif
I've figured out a formula that works but it seems overly complex - it uses multiple SUBSTITUTE, SEARCH and REPLACE functions. Is there an easy way to remove a range of symbols i.e. ! : ( ' ?
Is it possible to apply formatting to a formula in a cell when you are combining that formula with text? As an example, I want to format the following as a percentage: ="The result is"&" "&(a2/a1)
Currently, it is returning [e.g.] ...result is 0.5, instead of ...result is 50%
My formulas is
="Start Date: " & VLOOKUP('Project Information'!$C$4,Sheet1!A285:T285,19,0) & "
End Date: " & VLOOKUP('Project Information'!$C$4,Sheet1!A285:T285,20,0)
The results I want are Start Date: MMM-DD-YYYY End Date: MMM-DD-YYYY
What I am getting is: Start Date: 41537.7083333333 End Date: 41537.7083333333
So excel is give me the numeric values of the date instead of the formatted date. I tried to format the cell as date, but as I expected that didn't work because the cell also has text in it.
I need a formula for conditional formatting which highlights a row if A has the text "Ex" and B has "Paid".
View 2 Replies View RelatedI have a workbook with numerous worksheets that contain text, steps actually. I would like to select all common words and change their formatting to bold. Every time I try however, it formats the entire cell in bold. Is there a way to isolate just the word "click" for instance in all the cells in each worksheet and change the formatting to bold, and leave the other text within the cell normal?
View 9 Replies View RelatedTrying to create a button that, once clicked performs the following task:
Checks whether the text in a cell in the column X2:X40 matches the text in a cell in the Range A2:U14.
If it does, the formatting of the cell in X2:X40 (eg background) must change to match that of the corresponding cell in A2:U14.
Only 1 cell in the range will match 1 cell in the column
For example:
Before click:
Cell A9 has "John" written in it and a yellow background
Cell X4 has "John" written in it and a clear background
During click:
matches these cells
changes X4 so that it also has a yellow background
After click:
Cell A9 has "John" written in it and a yellow background
Cell X4 has "John" written in it and a yellow background
I have a large list composed of three columns, First name, Last name, and Email address. For the most part names and emails match up perfectly but there are an unknown number of names that do not match their email address. Here’s the hard part, email addresses are in different formats for example:
some are firstname.lastname@somewhere.com but others can be lastname.firstname@somewhere.com or some email addresses are first name initial then full last name with no dot separator @somewhere.com and even turned around like Last name initial then first name. For the most part this is the only four ways the emails are configured but all email addresses have some variation of last and first name in them.
How can I find the number of email addresses that do not match their names?
I warned you that it was hard.
I'm looking to see if I could use any sort of formula to have 1 number appear Red and another number appear Black all in one cell
In 1 Cell: 80 (75-95)
I'm looking to use this formula, where I'd like my A1 to be Red
=A1 & " " & A2
I have text in a cell like this:
MINOR Ø1.2314-1.2414 ([-XX-] "Minor" Ø1.23640 +/-.00500, SF1015-16)
The cell width is 35.71", this causes the text above to wrap. The part I am having trouble with is getting the text to be readable without portions of the text being split, such as:
MINOR Ø1.2314-1.2414 ([-XX-] "Minor" Ø1.23640 +/-.
.00500, SF1015-16)
Ultimately for readability I would like the text to read like this:
MINOR Ø1.2314-1.2414
([-XX-] "Minor" Ø1.23640 +/-.00500, SF1015-16)
Within the same cell.
The second string will always have a " (" at its beginning, a "space" and a "left bracket". Of course the string of text will always vary in length and some times only one character wraps or a majority of characters wrap.
I have to format huge text file to columns. Here is the file: [URL] The whole file should look like the first 15,16 rows. What is the fastest way that can be done? Here is the original file: [URL]
View 3 Replies View RelatedI am using Excel 2007. I want to put into a cell the symbol for the mean of a set of x values ie x-bar or x with a line over it. One way is to go to Word and use the equation builder, then copy it into Excel. However that restricts me to the default equation font, and I would prefer to be able to use my own choice of font. Is there a way to do this within Excel (perhaps by somehow combining two symbols, the x and a raised bar)? Alternatively can I do it within Word, without using the equation builder, and then copy it to Excel?
View 2 Replies View RelatedBasically, I have a Rule that says:
=$A1="Mitigated"
I want the same format if Column A says either "Mitigated" or "Accepted".
I can't create a new rule as I have to save the file in 2003 for other users and I've got another 2 rules for varoius other things.
I've tried crudely putting "Mitigated"OR"Accepted" and it dosen't work.
But the import tool I am using for one of our applications is requiring us to save using Excel 97-2003 Workbook. I have converted a couple coulumns to text, but inorder for the upload to take, I have to add an spostrophe infront of the number to make it "text" and upload cleanly. I want to do a find and replace to edit the columns with all the differing numbers. How do I do that? I tried to replace ?????? with '?????? and it doesn't work...
View 3 Replies View RelatedI have an excel spreadsheet that contains dates and times however it appears that some of the dates are in text format Is there a way i can format the data fist so they are all dates?
View 5 Replies View RelatedI 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 am automating an invoice template for an excel novice, thus I want to conditionally format certain cells dependant on what text is in them.
I want to do this in VB but I don't know how!
Why does a cell formatted as "text", display ### but shows the actual words when formatted as "general?"
View 13 Replies View RelatedI would like to conditional format a range of sells which contains numbers formatted as text. I would like if the sell contains the specific number (which is formatted as text) to be formatted in red. It works for one value but I have to list 15 values for which this condition to be done.
For example: if some of the cells in the worksheet contains one of the following values: 265987,265489,235698,2356987,2356987,2356987,235569,...ets - 15 conditions - to turns red.
I have a cell containing a post code (U.K). For example CF678JU however I need it to be formatted to show CF67 8JU. Is there a quick option of setting a formatting rule to add the space instead of me going into each cell and changing... (there's thousands of them!!!)
View 2 Replies View RelatedI have a program in unix that ouputs a text file in .csv form which allows me to import my data into an excel spreadsheet very easily. I am wondering if there is anyway I can add formatting, like to tell excel to make a cell green or something like that.
If it can't be done with .csv, is there any other tool or format available that will accomplish this?
When I export my files onto text and open it with Excel, it will automatically format the cells to take out leading zeros, convert to scientific notation, and convert certain numbers into date/month/year format. How can I get Excel to open my file without reformatting the numbers in my text file?
View 4 Replies View RelatedI am working with Excel 2000
If cell D4 has a 90 or above, I want cell C4 to highlight in green, but I also want the cell to display the word green. I know how to do the former, but the latter perplexes me a bit.
I have a table that has dates starting from A6 which is a whole month say 01/2/2013 to 28/02/2013 like a gantt template.
I need if the word "Ordered" is in B5 I need X (crosses) in the date cells starting from B6 to end of month until someone changes B5 to Delivered then I need the X to disappear.
test.xlsm
[URL] ....
A2 contains the word Yes. If A1 is blank, I need to clear the text from A2. Can this be done with conditional formatting? I know I could just change the text to white, but I actually need the text cleared from A2 if A1 is empty. My boss doesn't want me to use VBA so I can't code this.
View 8 Replies View RelatedI'm trying to reverse engineer a spreadsheet (see attached) and apply the data table to my own spreadsheet. The inspiration spreadsheet (attached) utilizes a 2 variable data table and conditional formatting, where the cells are formatted based on their value in relation to a variable called "Gross". So the rules look something like this:
Cell Value Between 0 and Gross-1
Cell Value > Gross
Cell Value = Gross
Cell Value < 0
Graded Color Scale Minimum ="<385000" Maximum =">385000"
I assume the Gross is calculated as 25% * 350,000 = 87,500. The hard coded value of 385000 is actually incorrect, because it's based on a percentage of 10%... but I'll let that slide The resulting formatting of the data table would support the hypothesis that "Gross" = 87,500. However, when I reset the Conditional Formatting to reference another cell with the calculation result of 87,500 it completely throws off the formatting. I'm simply stumped as to how they are using the text "Gross" as a variable. How/where are such variables set so they can be used in conditional formatting?
How to apply the data table logic itself to another spreadsheet.
I'm designing a spreadsheet for our department which tracks a students progress throughout their time at the school. There are 4 levelsStatementSchool Action PlusSchool ActionMonitorIn year 7, they may come into our school at Monitor level, but when they get to year 8 they may have moved to Action Plus. I want to highlight that change on my spreadsheet with conditional formatting if possible so if they go up, the cell will turn red, if they move down the list the cell will turn green.
I've created two columns on an options sheet (which holds all my data validation lists) and assigned a number to each of the levels so i can conditionally format my columns using < and > but I don't know how to write the lookup formula
What I'm trying to achieve
ExcelHelp.jpg
My Options sheet
Excelhelp2.jpg
When you programaticly copy the contents of a cell to a string, you loose all of the interior formatting of the cell. For example, if I have a cell where only part of the text is bold... |something like this sentence here| and I try to move it around programaticly (like store it in a database) I loose the bold font in the middle.
Is there any way to extract the rich text formatting out of a range object?