Excel: concatenation and formatting

Hi

How to bold part of a text in Microsoft Excel:

How do you bold only part of the text in a cell?
Without VBA, it's easy; just select it and click on the "bold" icon.
With VBA, you can use the position of the substring to do it:

*** :
Sub BoldText()
Range("A1") = "Automobile"
'Bold "bile"
Range("A1").Characters(7, 4).Font.Bold = True
End Sub
*** .

Denis Michon, (N°816)
Source => http://www.excelabo.net/excel/formatstexte.php

I think you can call a function, "BoldText()", the result during "calculation"; and this function call is located within the formula, in the right place. You create the function yourself, with help, based on the interesting example above. Thank you.

It's very hard to find information on the "style" function; to use in a formula!
For Microsoft Excel (it's difficult), like with OpenOffice.org Calc (easier to find information).
=> Do we create a style, and apply it directly in (from) the formula? (I'm in doubt)

Thank you.

See you later.

Hi

If you can "eriiic", please use "Microsoft Excel Viewer" to see how we view the thing; thank you.
=> This will be very instructive; no macros, VBA, or "other stuff" activated...

Also look with OpenOffice.org Calc; thank you; not everyone has Office...
Try, if you want and can, huh ;-), to make examples that are correctly visible to everyone.
(thanks)

For my part; I'm not saying to do it like the example proposed in Post #8:

B1: =A1&A2&A3&A4 (to concatenate)
But rather; if possible:
B1: =Bold(A1)&Italic(A2)&NeutralDefault(A3)&NeutralDefault(A4) (to concatenate)

=> We call the function; here for the text formatting, in the formula! ;-)
(Function that we do ourselves!!)

uh, in Excel, isn't it possible to use the hidden function "style", like in OpenOffice.org Calc?
I'm saying hidden, because I haven't found information... (please see the links in previous posts).

thank you "eriiic" and thank you all.

see you later

Good evening,

It's possible but your values will lose their numeric type, it will be a string.
C1: ="[“ & RIGHT("0000" & A1, 4) & “;” & RIGHT("0000" & B1, 4) & "]”

eric

I have the same kind of problem right now...
In fact, I want to assign a new format to the text written in my source cell, in my final cell...

Someone just gave me a good boost with the Excel TEXT formula...

I had a number format that I wanted to have in currency format... so my CONCATENATE now looks like this: =CONCATENATE(QW589;RJ589;TW589&TEXT(UB589;"# ## ##0_ $");UO589;QW590;QW591)

That said, I have another problem... I want RJ589 and TEXT(UB589;"# ## ##0_ $" to display their result in bold... how can I do that?

I want to proceed without a macro if possible, this file will operate on several computers....

Hello,
Have you tried looking among the functions offered in Excel?
Insert>Function, and you choose the options that suit you.

No Thierry, unfortunately I don't have a solution, but if someone gives you the right idea, I might also be able to solve my problem, which I raised a few minutes before yours.
See for info the thread: "data grouping"
We follow each other's responses, and we pass them on if it works.?
CRDLMNT
--
Science without conscience is but the ruin of the soul.

Yes, I looked at the functions offered by Excel, but I didn't find anything that could meet my needs (note that it's Excel in English, so I might have missed something).

If anyone knows how to achieve the same result with a macro, I'm open to it.

Thank you in advance.

Good evening,

Here’s a clue:
Let A1 = "Hello
A2 = "how are you"
A3 will contain the following formula:
=concatenate(a1;" ";a2)
Well, I don't have Excel on hand, so I don’t know if the bold formatting of "Hello" is preserved.
To be tested.

See you later
zarakoff

Exactly, that is my problem, the formula does not keep the cell formatting, so we simply get Hello how are you.
And by making the cell bold, we get Hello how are you.
So there's no way to have Hello how are you like this.

Hello

How to bold part of a text in Microsoft Excel:

https://sebsauvage.net/temp/wink/excel_vbarecord.html

Source => http://www.commentcamarche.net/forum
/affich-2073115-excel-comment-mettre-en-gras-avec-une-macro

XL4 "hidden" functions; Recent tip: read.cell(); on (C) 2006 Excel Downloads:
https://www.excel-downloads.com/threads/astuce-recente-lire-cellule.32955/

Know if the selected cells (Excel) are bold and italic:
http://www.codyx.org/snippet_savoir-si-cellules-selectionnees-excel-sont-gras-italiques_336.aspx

 Public Function IsBoldItalic(ByVal MyRange As Range) As String Dim rCell As Range For Each rCell In MyRange IsBoldItalic = IsBoldItalic & rCell.Address & " :" & vbCrLf IsBoldItalic = IsBoldItalic & vbTab & "Bold : " & CBool(rCell.Font.Bold) & vbCrLf IsBoldItalic = IsBoldItalic & vbTab & "Italic : " & CBool(rCell.Font.Italic) & vbCrLf Next rCell End Function Sub EXAMPLE() Debug.Print IsBoldItalic(Range("A1:A4")) ' *** RESULT : *** '$A$1 : ' Bold : False ' Italic : False '$A$2 : ' Bold : True ' Italic : False '$A$3 : ' Bold : False ' Italic : True '$A$4 : ' Bold : True ' Italic : True End Sub 

thank you.

see you!

Thank you Anonymous for all this research, but I didn't find anything that corresponded to my problem.

In fact, it's more complicated than just bolding a part of the cell.
Another example:
in A1: Hello_
in A2: how are you_
in A3: yes_
in A4: and you?
in B1: =A1&A2&A3&A4 (to concatenate)
and I get:
Hello_how are you_yes_and you?
instead of
Hello_how are you_yes_and you?

Basically, I want to keep the formatting when concatenating.

Thank you in advance.

Hello Thierry,

Not easy but interesting your question.
I initially wanted to create a function replacing CONCATENATE but it was impossible to solve.
So I created a sub that analyzes the formula of the cells you pass to it by selecting ranges and pastes the result into the cell immediately to the right.
I don't do much checking, so these formulas should be limited to = A1 & A2 & ... and refer to cells that only contain strings. The retrieved attributes are: bold, italic, single underline, color. I haven't looked into it, but retrieving the font and size should be possible.

Of course, it's just a first draft but it's functional. It will need to be completed with a user form allowing the choice of the destination offset, the attributes to be retrieved, etc. I also planned to be able to insert a line break wherever wanted (for Vaucluse ;-)) but it hasn't been addressed yet.
A more illustrative example: http://www.cijoint.fr/cij45591193727506.xls

You select for example F3:F4 and F6:F8 and run the macro RecupFormatCel
You'll be able to create nice gradients ;-)

Eric

Re,

new version that allows inserting a string "vbLf" forcing a line break within the text of a cell
http://www.cijoint.fr/cij2036478627532.xls

Be careful to always reference a cell, I am not (yet?) dealing with formulas like =A1 & "blablabla" & A3 & ...
eric

Hi Anonymous,

For Open Office, I have never looked into macros. Probably easily adaptable.
As for making a function, that was my initial idea but it’s not that easy, believe me. As soon as you return the value, there’s no way to continue processing to format it and access the address without creating a circular reference, and if you try it differently, you lose the attributes already set... (need to investigate further but for now, that's where I am)
At the limit, the option to create a procedure has an advantage: being able to process a table that is already made as long as we clear the cells receiving the result, but then there’s no dynamic update :-s
But well, there are possible improvements; it was initially to see feasibility without doing too many checks or in-depth analysis of the formula.

As a substitute for the viewer, here’s a screenshot.
The concatenation formulas are in column H, the result of the macro in column I
I chose a specific string for line breaks, hence the “vbLf”
http://www.cijoint.fr/cij73148756327527.jpg

The VBA code for adaptation in Open Office:

Const LF As String = "vblf" Sub RecupFormatCel() Dim c1 As Range, c2 As Range, dest As Range Dim i As Integer, long1 As Long, ptr1 As Long, ptr2 As Long Dim formatCel As Variant Dim ListeRef As Variant For Each c1 In Selection f = c1.Formula If Left(f, 1) <> "=" Then 'formula ? Exit Sub Else f = Mid(c1.Formula, 2) 'yes: remove = End If Set dest = c1.Offset(0, 1) 'destination cell dest.Value = c1.Value ListeRef = Split(f, "&") ' split the formula ' ' replacement of "vbLF" with vbLf While InStr(1, LCase(dest.Value), LF) pos = InStr(1, LCase(dest.Value), LF) dest = Left(dest.Value, pos - 1) & vbLf & Mid(dest.Value, pos + Len(LF)) Wend ' recovery of formats ptr1 = 1 For i = 0 To UBound(ListeRef) Set c2 = Range(ListeRef(i)) ' address of the string long1 = Len(c2.Value) ' length of the string 'ptr2 = ptr2 + long1 If LCase(c2.Value) = LF Then ' process vbLF long1 = 1 Else With c2.Font formatCel = .FontStyle dest.Characters(Start:=ptr1, Length:=long1).Font.FontStyle = formatCel formatCel = .ColorIndex dest.Characters(Start:=ptr1, Length:=long1).Font.ColorIndex = formatCel formatCel = .Underline dest.Characters(Start:=ptr1, Length:=long1).Font.Underline = formatCel End With End If ptr1 = ptr1 + long1 Next i Next c1 End Sub Sub test() Range("H3:H5,H7:H8").Select RecupFormatCel End Sub Sub raz() Range("I3:I8").ClearContents End Sub

eric

Hello,

Thank you for looking into my question so much, I wasn't expecting that. Obviously, there isn't too complicated of an answer.
But manually bolding the amount of data I have is well below the time spent trying to do it quickly, so there isn't much added value.

If you want to keep looking for a solution, that's fine, I'll keep an eye on the discussion, but from my perspective, it's simpler to let it go and do it by hand. Thanks to eriiic and to anonymous.

Ciao
Thierry

Hello

thank you very much "eriiic" for the information, the help, and the conversion for OpenOffice.org Calc!

(thanks to everyone as well for thinking about the problem) or the issues raised in the replies! :-)

(Let’s try to provide a solution to all the questions asked here, indirectly in the responses)

* The spreadsheet, Microsoft Excel or OpenOffice.org Calc, is not easy! ...

thank you "eriiic", and thank you all for more information and help. thanks.

see you later

Last version with offset choice: http://www.cijoint.fr/cij77852548227558.xls
(offset 0 replaces formulas with formatted strings)

Const LF As String = "vblf"
Sub RecupFormatCel()
Dim c1 As Range, c2 As Range, dest As Range
Dim i As Integer, long1 As Long, ptr1 As Long, offset1 As Long
Dim formatCel As Variant
Dim ListeRef As Variant
Dim msg As String
msg = "Which offset (in columns) to paste the result?" & vbCrLf
msg = msg & "(if offset = 0 the original formula " & vbCrLf
msg = msg & "will be replaced by the formatted string)"
offset1 = InputBox(msg, "Choose result offset")

For Each c1 In Selection
f = c1.Formula
If Left(f, 1) <> "=" Then 'formula?
MsgBox ("Error" & vbCrLf & "Cell " & c1.Address & " does not contain a concatenation formula")
Exit Sub
Else
f = Mid(c1.Formula, 2) 'yes: eliminate =
End If
Set dest = c1.Offset(0, offset1) 'destination cell
dest.Value = c1.Value
ListeRef = Split(f, "&&") 'split the formula
'
' replacement of "vbLF" by vbLf
While InStr(1, LCase(dest.Value), LF)
pos = InStr(1, LCase(dest.Value), LF)
dest = Left(dest.Value, pos - 1) & vbLf & Mid(dest.Value, pos + Len(LF))
Wend

' retrieving formats
ptr1 = 1
For i = 0 To UBound(ListeRef)
Set c2 = Range(ListeRef(i)) 'address of the string
long1 = Len(c2.Value) 'length of the string
If LCase(c2.Value) = LF Then
' vbLF processing
long1 = 1
Else
With c2.Font
formatCel = .FontStyle
dest.Characters(Start:=ptr1, Length:=long1).Font.FontStyle = formatCel
formatCel = .ColorIndex
dest.Characters(Start:=ptr1, Length:=long1).Font.ColorIndex = formatCel
formatCel = .Underline
dest.Characters(Start:=ptr1, Length:=long1).Font.Underline = formatCel

End With
End If
ptr1 = ptr1 + long1
Next i
Next c1
End Sub
Sub test()
Range("H3:H5,H7:H8").Select
RecupFormatCel
End Sub
Sub raz()
Range("I3:I8").ClearContents
End Sub

To follow
--
(_Usul of Arrakis
Serial_Dreamer_)

Very nice demo for styles applied to text, but what about when it comes to a custom format?

Example:

Entered values: A1=5 B1=10
Displayed values: A1=0005 B1=0010

And I would like to concatenate the values while preserving their format (e.g., C1 = [0005;0010]

Is that possible?

Thank you for your help!

...I really need to get into macros -_-