=TEXT(REF, UNDERLINE FORMAT) - how?

P

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,750
I have a value (numeric) in cell A1.

Then in cell B1, I have the follwoing formula:

="The result is"&A1.

So if I have a number 50 in A1, B1 will look like:

The result is 50.

How do I make that

The result is 50.

??

I reckon I need to use the TEXT(REF,FORMAT) function but I do not know how to express the UNDERLINE format in the custom formatting.

Thanks
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Hi PATSYS

Unfortunately it's not possible. You have a formula in B1 and any cell that has a formula has all its characters with the same format.

You can only have different formats in the same cell if what the cell has is text (no formula).

If you really need the result you show, you have to implement it with the Change Event, monitoring the value in A1 and changing the thext in B1 accordingly.
 
Upvote 0
As a worksheet change event


If Target.Column = 1 And Target.Row = 1 Then
Range("B1").Select

ActiveCell.FormulaR1C1 = "=""The result is ""&RC[-1]"


Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B1").Select
Application.CutCopyMode = False


With ActiveCell.Characters(Start:=15, Length:=2).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleSingle
.ColorIndex = xlAutomatic
End With
End If
 
Upvote 0
SteveO59L:

I don't think it's safe to say that the only number would only be 2 digits long, but there is no need to do all that selecting. Ranges can be worked with directly in VBA. You'll also want to use Application.EnableEvents to prevent the code from calling itself.


PATSYS:

Perhaps:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Range("B1")
    .Value = "The result is " & Range("A1")
    .Characters(15).Font.Underline = True
    .Font.Bold = True
End With
Application.EnableEvents = True
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Reaaly basic question - deleting "html link" text
Replies
2
Views
102
Malthus101
M
A
  • Question
Index and Match for 2No criteria only partially returning values other values #ref error.
Replies
6
Views
132
roelandwatteeuw
R
P
  • Solved
format text in multiple cells
Replies
2
Views
248
paulyankson
P
P
  • Question
Named ranges and dashboard
Replies
5
Views
141
Jeffrey Mahoney
Jeffrey Mahoney
A
  • Question
Using underlined text as a condition in IF formula
Replies
4
Views
945
RoryA
RoryA

Forum statistics

Threads
1,214,904
Messages
6,122,169
Members
449,070
Latest member
webster33

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top