Formating query

I use excel as a database for storing / manipulating info 
for my web site which deals with bird checklists. The bird 
names are entered as follows... Common Buzzard (bold)
space, Buteo buteo (italicised). My problem is that I have 
to merge the two tables (bold name) & (latin name in 
italics) into one cell but I cannot get the formating to 
stay. That is, the italicised name always follows the 
original cell format. Is there a symbol or control symbol 
I can insert between the bold and italic names to maintain 
the formating? I can change the format manually but with 
20,000 entries to change it might take some time!

thanks
0
mal92 (1)
2/16/2004 11:52:00 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
366 Views

Similar Articles

[PageSpeed] 25

You can't mix formatting in cells that contain formulas.  But you could have a
macro that combines the values and formats the resulting value.

I don't know how your table is laid out, but I guessed Column A and Column B. 
And I put the resulting string in column C.

Option Explicit
Sub testme01()

    Dim myCell As Range
    Dim myRng As Range
    
    Set myRng = Selection.Columns(1)
    
    For Each myCell In myRng.Cells
        With myCell.Offset(0, 2)
            .Value = .Offset(0, -2).Value & " " & .Offset(0, -1).Value
            .Characters(1, Len(.Offset(0, -2).Value)).Font.Bold = True
            .Characters(Len(.Offset(0, -2).Value) + 1).Font.Italic = True
        End With
    Next myCell
            
End Sub

Select your range and try running this macro.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Mal wrote:
> 
> I use excel as a database for storing / manipulating info
> for my web site which deals with bird checklists. The bird
> names are entered as follows... Common Buzzard (bold)
> space, Buteo buteo (italicised). My problem is that I have
> to merge the two tables (bold name) & (latin name in
> italics) into one cell but I cannot get the formating to
> stay. That is, the italicised name always follows the
> original cell format. Is there a symbol or control symbol
> I can insert between the bold and italic names to maintain
> the formating? I can change the format manually but with
> 20,000 entries to change it might take some time!
> 
> thanks

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
2/17/2004 2:20:15 AM
By definition, data is not formatted. Persist the raw values in the
database and do the formatting in the front end. Similarly, have two
separate columns for English name and Latin name respectively and do
the concatenation (with parentheses) in the front end.

--

"Mal" <mal92@tiscali.co.uk> wrote in message news:<10e1201c3f4e7$dea20a50$a301280a@phx.gbl>...
> I use excel as a database for storing / manipulating info 
> for my web site which deals with bird checklists. The bird 
> names are entered as follows... Common Buzzard (bold)
> space, Buteo buteo (italicised). My problem is that I have 
> to merge the two tables (bold name) & (latin name in 
> italics) into one cell but I cannot get the formating to 
> stay. That is, the italicised name always follows the 
> original cell format. Is there a symbol or control symbol 
> I can insert between the bold and italic names to maintain 
> the formating? I can change the format manually but with 
> 20,000 entries to change it might take some time!
> 
> thanks
0
onedaywhen (103)
2/18/2004 11:04:47 AM
Reply:

Similar Artilces:

Where is the Keep Text Formatting feature located in Word 07
I believe this Keep Text Formatting feature might be what I need, but I have been unable to locate exactly where it is located in Word 2007. I'm trying to rid a Word document sent to me of tables, text boxes, graphics and all other document formatting, while retaining the document's text content. It is unimportant to me whether the text formatting is retained or not. Thanks. Are you referring to a Keep Text Formatting feature in an earlier version of Word? I wonder whether what you're looking for is "Paste Unformatted," since you seem to be saying you _don...

Conditional format #12
Hi. I am trying to set a conditional format. If NETWORKDAYS(A1,TODAY())> 3 I would like the cell to format with red shading. How do is set the formula in the condtional statement? I tried ="NETWORKDAYS(A1,TODAY())>3" , but this doesn't seem to work. Thanks, Mike. Hi Mike! It doesn't work because the NETWORKDAYS function is in another workbook (the Analysis ToolPak add-in). You can use a workaround by putting your condition in a separate cell (eg B1) and then referring to that cell: B1 =NETWORKDAYS(A1,TODAY()) Then your conditional format for A1 becomes:...

formating color
hi i have a formating question. i have cells with number choices i them. for example the cell looks like this. 100, 105, 123, 041, 514, 455 now i want to go back and select one of these. i wanted to highligh the one that was chosen. but it will not let me. is there a way i ca do this? it will only allow me to highlight the entire cell, not jus parts of it. thank you in advanc -- RAPPE ----------------------------------------------------------------------- RAPPEL's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3624 View this thread: http://www.excelforum.com...

How can I customize the Filter Drop down on the formatting toolbar
I want to clear everything out of it, except the customized filters I have created. Can i do that? Project > Filter by > More Filters, then select each one, Edit, and remove the checkbox for Show in Menu. - Andrew Lavinsky Blog: http://blogs.catapultsystems.com/epm > I want to clear everything out of it, except the customized filters I > have created. Can i do that? > I've tried that - it doesn't affect that drop down filter list - whether they are checked or not. "Andrew Lavinsky" wrote: > Project > Filter by > Mo...

Formatting
I received a pub doc from a third party. It was formatted as a 11x16 size. Once I decreased it to 8.5x11 and view the doc under 'print preview' the entirety of the image did not view. Basically, the image didn't shrink with the size. Any input? Patty wrote: > I received a pub doc from a third party. It was formatted as a 11x16 size. > Once I decreased it to 8.5x11 and view the doc under 'print preview' the > entirety of the image did not view. Basically, the image didn't shrink with > the size. Any input? Publisher doesn't resize the contents o...

Formatting multiple object types in output from a function?
I'm stumped on this one, and have searched and searched for a solution or how-to with no luck. Perhaps it just can't be done. I have a function that may or may not return objects of multiple types. I've setup the type and formatting information for each of the individual types in format.ps1xml files, and when only 1 type is returned by the function the formatting works as I would expect. However, if two or more types are returned by the function, only the formatting for the first object type is applied and then the other types are displayed however PoSH deems ap...

Conditional Format #20
I see that I can only use 3 conditional formats. I need a few more than 3 is there any way to do this. Kevin Try this free add-in on Bob Phillips' website: http://www.xldynamic.com/source/xld.CFPlus.Download.html Another possibility (albeit limited) is to combine your conditional formats with custom formatting. See J.E. McGimpsey's site for an example: http://www.mcgimpsey.com/excel/conditional6.html HTH Jason Atlanta, GA >-----Original Message----- >I see that I can only use 3 conditional formats. I need a few more than 3 is >there any way to do this. > >Kev...

Calling VBA subroutine from a query?
Can I call a VBA subroutine from within an Access query? I wrote some English to Metric conversion routines in the Access VBA code and would like to run a query on the data that will return coverted values. I need to be able to execute this from outside the database (run the query from another program). Yes. The function must be a Public Function in a standard module. You use a calculated control to return the results of the query. ConvertedValue: MyFunction([SomeField]) -- Dave Hargis, Microsoft Access MVP "DavidY" wrote: > Can I call a VBA subroutine from within an ...

Button on Form to Run Report based on Query with Parameters
Hi Guys. I am busy working on a stock control database (and job control). When I have a Form Open, it shows several fields, including JobID (Which is the PK for that job) (frm_Invoicing) I also have a Query that when Run, it prompts for the JobIDNo. (qry_CustJobsInvStock) I have a report based on that query. (rpt_CustJobsInvStock) I have added a button onto the form (onclick -> DoCmd.OpenReport) to open and automatically print the report, But I just cannot figure out how to get it to get my JobID from the form, and to Automatically add it as a Parameter for the que...

Conditional formatting / blank cells
Hello, I need help with a Conditional Format. This is my worksheet. Row 4 A B C D E F G H I $200 $210 I want a conditional format in G4 that states if G4 is greater than or equal to F4 the fill colour is green. If G4 is less than F4, the fill colour is red. If G4 is blank, the fill colour is white. I've tried numerous combinations, but cannot seem to get this to work. Thanks torkattack. Test for the blank first. -- David Biddulph "torkattack" <torkattack@discussions.microsoft.com> wrote ...

Excel could not save all the data and formatting you recently added
Hi, One of our users sent me an Excel file of 6 MiB. It has 7 worksheets. Most of them have <100 rows and AH columns, one sheet has 13160 rows and AH columns. The large sheet has autofilter enabled, but no actual filtering is done. (yet) 4 columns have validation: they allow a list of values specified in a range somewhere else in the sheet. There is also conditional formatting. It takes >30 seconds to calculate the sheet, however there are no real calculations, just a few concatenated string. My first impression is that this is yet another example of Excel (ab)used as a database. The p...

PDF format pagination
I have several reports utilizing Landscape layout. I followed the Report Writers Guide from the SDK with the Width at 11 and Height 8.25 or smaller. However, when I export it to the PDF format, the layout shows up Portrait. How could I change this? The export is fine in Excel with the correct margin setup. Thanks. ...

CSV, Milliseconds default formatting of mm:ss.0
When I generate some CSV files I need to include milliseconds. When anyone opens the file in Excel (in my case part of Office 2003, SP2) the datetimes appear with a default formatting of mm:ss.0. This is frustrating and appears unprofessional on my part when clients open the file. They assume I have made some sort of mistake. I have already thought of generating using =DATE(blah)+TIME(blah)+millis/(24*60*60*1000) but I don't like the idea. How can I change the default format so that it would be something more sensible and my clients and I could avoid the drudgery of clicking on the...

Conditional formatting formula with multiple criteria
Hi all, Have been puzzling over this for a while now, and can't get it right! Using conditional formatting, I want to: If any of the cells in Column B are blank, and the cells in Column C are blank, and the date in Column D is less than todays date - 3 days, turn the cell bold and red Can someone please help? I have tried all different variations and just can't get it! Thanks, Kirstie Kirstie Your questions sound like you wish to pick up whether there is a blank ANYWHERE in column B AND (not or) ALL cells in column C are blank AND that the date in a single cell in colum...

Export to comma-delimited format doesn't seem to work
I apologize for the elementary question. I have an Excel spreadsheet which is simply a list of email addresses. I want to export them to a format allowing me to paste them into the "To" field of an email. (In case you're concerned, this is not unsolicited bulk commercial email). I've been able to export them to a .csv format, but it doesn't seem to have the commas, which confuses me and my email software. Any help will be much appreciated. -- Patty Ayers | www.WebDevBiz.com Free Articles on the Business of Web Development Web Design Contract, Estimate Request Form...

Matching Zip coded in a Query
I have query that joins two tables using the Zip Codes (only show the records where the Zip codes match). The problem is that in one table, the listing of zip codes, it is 5 digit zip codes. In the main table some of the zip codes are 5+4 zip codes. My current query leave out the 5+4 Zip codes. I want all the records as long as the 5 digit zip codes matches. I quess I could delete the - and the last 4 digits on the main table, but it would be nice to retain the whole zip codes. Is there a way to do this? Thanks!!!!! Use Left(MainTableZipCodeField,5) to compare to the original ...

Formating, paragraph marks, boxes etc, how do I get rid of them?
One day I opened up excel and it had all these marks for formating in my form. This also happened in word. How do I get rid of them? Josh: In Word, paragraph marks can be turned on/off by clicking the paragraph icon (looks like a capital P but with two, vertical strokes instead of one). I just finished wandering through Excel 2000 looking for the same icon and the only mention of the paragraph mark is related to object linking and embedding. I've also done a lot of forms in Excel and don't remember ever running into paragraph marks. Are you using OLE in the spreadsheet...

Conditional Formatting
I am using the below to auto change color if it meets the condition... =IF(F26>=0,IF(OR((F26)<=2,(F26)>=-1),"GREEN", IF(AND((F26)<=2.5,(F26)<=-1.5),"AMBER","RED")), IF(AND((F26)>=2,(F26)<=-1),"GREEN", IF(AND((F26)>=2.5,(F26)>=-1.5),"AMBER","RED"))) Any help is appreciated. -- J~ In 2003, when you set up a conditional format, you can choose one color for the condition. If you want to do something like what you are showing, you're going to need to use a Worksheet_Change event and code something ...

conditional format of data tables in charts
I've created a chart that is based on a data set where the cells have conditional formatting (if value a > value b then colour the cell red) I have the data table displayed with the chart (in chart options, 'show data table' is ticked), but the condtional formatting is not displayed in this table, i.e cells that are red in my original data set are not coloured in the chart table. Is there any way to apply the conditional format on the table displayed within the chart? Cheers Dan. Daniel, Unfortunately you can't apply conditional formating to data tables. However, yo...

Queries and reports in access Need help please
Hello everyone , I have a little issue to setup a database. I created a table with approximately 20 columns. The columns have an entry of Yes or no base on a questionnaire from survey. What can I do to get a result of my table I would like to know how many "Yes" and "no" I have. I don't know how to add them or get this information. Thanks in advance GABRIEL Gabriel, The first answer to your question is that your table structure is not really the best for this situation, and it will be a lot easier if you can set it up differently. Is your database design set in ...

Formatting the Date in Excel
Is there any way in Excel that I can format the Date so I don't have to type any slashes between the month and day? In other words, I would like to be able to type in 0714 press "enter" and have excel recognize that this is 07/14/2005 without typing in 07/14. Any suggestions would be appreciated. Thanks, Short of writing code, you could enter your numerics only date in cell A1, for instance: 07142005. Then in B1 write a formula: =mid(A1,1,2)&"/"&mid(A1,3,2)&"/"&left(A1,4) Then you could copy column B and paste as values into their req...

Query repeated values
Hello everyone. Sorry for such a newbie doubt, but since my Access and SQL experience is a bit “trial and error” I don’t even know what to search for here in the forums that so I can help myself. I have two tables A and B. First table has people’s NAMES field. Marc Wilson Andrea Smart Francis Junior Second table has a field with people’s ABILITIES but some are repeated: WORD – Marc Wilson WORD – Andrea Smart EXCEL – Francis Junior COREL – Andrea Smart COREL – Andrea Smart I’m trying to build a query to point out the duplicated lines, in this case: COREL – Andrea Smart ...

Hpw can I use the same label format?
I created a label to print some selected records from Access with the Filter option. Next time, if I want to print different records from the same Access file. How can I use the same label created, instead of recreating the same label format everytime I want to print other records? Instead of including the filter in the label report, leave the report unfiltered so that by default it would print all labels. You can then restrict which labels are printed at runtime in various of ways: 1. Change the report's RecordSource to a query with one or more parameters which prompt yo...

Conditional Formatting In a Datasheet Form
Hi All, I am trying to “Condition Format” a column on my form so it will highlight the individual cell if another cell in the same column has the same value. The form is in Datasheet view and it can not be changed from that due to the way I built the database. I have tried to build the condition format like so, Condition1 “Field Value Is” “equal to” [Column Name] and all it dose is it highlight the cell in the column when I input a record into it, regardless if it is a duplicate or not. Is there a way to accomplish this? Thank you for your time, Cage On Thu, 1 Mar 2007 10:34:06 -...

union queries
I need to build a query which combine records from a number of similar tables. Building a union query works great with "normal" records. Problem is, these tables contain attachments in some fields and MS ACCESS 2007 treat these fields as multi-value fields and does not allow building APPEND or UNION queries with multi-value fields. I am so frustrated with this, because I would like to base my searches and some other queries on this. PLEEEESe help Cheers Dawie Theron On Fri, 26 Feb 2010 05:54:01 -0800, Dawie Theron <DawieTheron@discussions.microsoft.com&g...