Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to format selective part in a text
Hi all,
In MS Excel, in th below formula what additional formula do I add to make selective text in different formats (ex. bold)? Ex: ="Mission 1 Start Date: "&text(A1,"DD-MMM-YYYY") In the result I want only the date part of the result text to be in bold letters. Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to format selective part in a text
No can do in a FORMULA. You must convert to a string
-- Don Guillett Microsoft MVP Excel SalesAid Software "Gladiator" wrote in message ... Hi all, In MS Excel, in th below formula what additional formula do I add to make selective text in different formats (ex. bold)? Ex: ="Mission 1 Start Date: "&text(A1,"DD-MMM-YYYY") In the result I want only the date part of the result text to be in bold letters. Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to format selective part in a text
Sorry; you cannot change the formats using formulas.....
-- Jacob (MVP - Excel) "Gladiator" wrote: Hi all, In MS Excel, in th below formula what additional formula do I add to make selective text in different formats (ex. bold)? Ex: ="Mission 1 Start Date: "&text(A1,"DD-MMM-YYYY") In the result I want only the date part of the result text to be in bold letters. Thanks in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to format selective part in a text
Thanks guys.
"Don Guillett" wrote: No can do in a FORMULA. You must convert to a string -- Don Guillett Microsoft MVP Excel SalesAid Software "Gladiator" wrote in message ... Hi all, In MS Excel, in th below formula what additional formula do I add to make selective text in different formats (ex. bold)? Ex: ="Mission 1 Start Date: "&text(A1,"DD-MMM-YYYY") In the result I want only the date part of the result text to be in bold letters. Thanks in advance. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to format selective part in a text
Only text strings constants can have different formatting for a portion of
its characters and, unfortunately, formulas do not produce text string constants. If you are able to use VB code, we can mimic the dynamic action of a formula while at the same time putting text string constants with individualized character formatting in specified cells. Right click the tab at the bottom of the worksheet where you want this functionality, click View Code from the popup menu that appears and copy/paste only ONE of the following two into the code window that appeared (read the comments above the line of equal signs to see which one to use)... 'If the date in A1 is typed in (that is, A1 does NOT contain a formula) '================================================= ======================= Private Sub Worksheet_Change(ByVal Target As Range) Const ResultRange As String = "E4" Const RangeToMonitor As String = "A1" If Not Intersect(Target, Range(RangeToMonitor)) Is Nothing Then Range(ResultRange).Value = "Mission 1 Start Date: " & _ Format(Target.Value, "DD-MMM-YYYY") Range(ResultRange).Characters(23, 11).Font.Bold = True End If End Sub 'If A1 contains a formula which display the date '================================================ Private Sub Worksheet_Change(ByVal Target As Range) Const ResultRange As String = "E4" Const MonitorRange As String = "A1" If Not Intersect(Target, Range(MonitorRange).Precedents) Is Nothing Then Range(ResultRange).Value = "Mission 1 Start Date: " & _ Format(Range(MonitorRange).Value, "DD-MMM-YYYY") Range(ResultRange).Characters(23, 11).Font.Bold = True End If End Sub Note: You have to change at least one of the assignments in whichever of the above code modules you use. These values are in the first two lines of each code module and they begin with the keyword "Const". The one named ResultRange, where I have assigned "E4" to it, is the address where your "Mission 1 Start Date" formula is in... change the "E4" I used to your actual cell's address. The one named MonitorRange, where I have assigned "A1" to it, is the address where the date your "Mission 1 Start Date" formula references... A1 is what you told us in your posting, but you can change it if A1 was an example and not the actual cell containing the date that you want to be bolded in the ResultRange cell. -- Rick (MVP - Excel) "Gladiator" wrote in message ... Hi all, In MS Excel, in th below formula what additional formula do I add to make selective text in different formats (ex. bold)? Ex: ="Mission 1 Start Date: "&text(A1,"DD-MMM-YYYY") In the result I want only the date part of the result text to be in bold letters. Thanks in advance. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to format selective part in a text
Rick, i could not get it to work.
"Rick Rothstein" wrote: Only text strings constants can have different formatting for a portion of its characters and, unfortunately, formulas do not produce text string constants. If you are able to use VB code, we can mimic the dynamic action of a formula while at the same time putting text string constants with individualized character formatting in specified cells. Right click the tab at the bottom of the worksheet where you want this functionality, click View Code from the popup menu that appears and copy/paste only ONE of the following two into the code window that appeared (read the comments above the line of equal signs to see which one to use)... 'If the date in A1 is typed in (that is, A1 does NOT contain a formula) '================================================= ======================= Private Sub Worksheet_Change(ByVal Target As Range) Const ResultRange As String = "E4" Const RangeToMonitor As String = "A1" If Not Intersect(Target, Range(RangeToMonitor)) Is Nothing Then Range(ResultRange).Value = "Mission 1 Start Date: " & _ Format(Target.Value, "DD-MMM-YYYY") Range(ResultRange).Characters(23, 11).Font.Bold = True End If End Sub 'If A1 contains a formula which display the date '================================================ Private Sub Worksheet_Change(ByVal Target As Range) Const ResultRange As String = "E4" Const MonitorRange As String = "A1" If Not Intersect(Target, Range(MonitorRange).Precedents) Is Nothing Then Range(ResultRange).Value = "Mission 1 Start Date: " & _ Format(Range(MonitorRange).Value, "DD-MMM-YYYY") Range(ResultRange).Characters(23, 11).Font.Bold = True End If End Sub Note: You have to change at least one of the assignments in whichever of the above code modules you use. These values are in the first two lines of each code module and they begin with the keyword "Const". The one named ResultRange, where I have assigned "E4" to it, is the address where your "Mission 1 Start Date" formula is in... change the "E4" I used to your actual cell's address. The one named MonitorRange, where I have assigned "A1" to it, is the address where the date your "Mission 1 Start Date" formula references... A1 is what you told us in your posting, but you can change it if A1 was an example and not the actual cell containing the date that you want to be bolded in the ResultRange cell. -- Rick (MVP - Excel) "Gladiator" wrote in message ... Hi all, In MS Excel, in th below formula what additional formula do I add to make selective text in different formats (ex. bold)? Ex: ="Mission 1 Start Date: "&text(A1,"DD-MMM-YYYY") In the result I want only the date part of the result text to be in bold letters. Thanks in advance. . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to format selective part in a text
Tell me what is in A1... a manually entered date or a formula that displays
a date. If a manually entered date, what is the Cell Format for that date? If a formula, copy/paste it in your response so we can see what you are working with. -- Rick (MVP - Excel) "Gladiator" wrote in message ... Rick, i could not get it to work. "Rick Rothstein" wrote: Only text strings constants can have different formatting for a portion of its characters and, unfortunately, formulas do not produce text string constants. If you are able to use VB code, we can mimic the dynamic action of a formula while at the same time putting text string constants with individualized character formatting in specified cells. Right click the tab at the bottom of the worksheet where you want this functionality, click View Code from the popup menu that appears and copy/paste only ONE of the following two into the code window that appeared (read the comments above the line of equal signs to see which one to use)... 'If the date in A1 is typed in (that is, A1 does NOT contain a formula) '================================================= ======================= Private Sub Worksheet_Change(ByVal Target As Range) Const ResultRange As String = "E4" Const RangeToMonitor As String = "A1" If Not Intersect(Target, Range(RangeToMonitor)) Is Nothing Then Range(ResultRange).Value = "Mission 1 Start Date: " & _ Format(Target.Value, "DD-MMM-YYYY") Range(ResultRange).Characters(23, 11).Font.Bold = True End If End Sub 'If A1 contains a formula which display the date '================================================ Private Sub Worksheet_Change(ByVal Target As Range) Const ResultRange As String = "E4" Const MonitorRange As String = "A1" If Not Intersect(Target, Range(MonitorRange).Precedents) Is Nothing Then Range(ResultRange).Value = "Mission 1 Start Date: " & _ Format(Range(MonitorRange).Value, "DD-MMM-YYYY") Range(ResultRange).Characters(23, 11).Font.Bold = True End If End Sub Note: You have to change at least one of the assignments in whichever of the above code modules you use. These values are in the first two lines of each code module and they begin with the keyword "Const". The one named ResultRange, where I have assigned "E4" to it, is the address where your "Mission 1 Start Date" formula is in... change the "E4" I used to your actual cell's address. The one named MonitorRange, where I have assigned "A1" to it, is the address where the date your "Mission 1 Start Date" formula references... A1 is what you told us in your posting, but you can change it if A1 was an example and not the actual cell containing the date that you want to be bolded in the ResultRange cell. -- Rick (MVP - Excel) "Gladiator" wrote in message ... Hi all, In MS Excel, in th below formula what additional formula do I add to make selective text in different formats (ex. bold)? Ex: ="Mission 1 Start Date: "&text(A1,"DD-MMM-YYYY") In the result I want only the date part of the result text to be in bold letters. Thanks in advance. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can i format (colour) selective text in a cell using excel2000? | Excel Discussion (Misc queries) | |||
Text as part of Cell Format | Excel Discussion (Misc queries) | |||
Format Part of Text Cell using VBA | Excel Programming | |||
format part of text in a cell? | Excel Discussion (Misc queries) | |||
How do I format text that is part of a formula? | Excel Discussion (Misc queries) |