Formula to format selective part in a text

  • Thread starter Thread starter Gladiator
  • Start date Start date
G

Gladiator

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.
 
Thanks guys.

Don Guillett said:
No can do in a FORMULA. You must convert to a string

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)


.
Click to expand...
 
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, i could not get it to work.

Rick Rothstein said:
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)





.
Click to expand...
 
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.
 
Back
Top