Excel: concatenation and formatting
thierry
-
Visiteur -
Visiteur -
Hi,
I have a concatenation issue with Excel.
An example: better than a long speech:
in my first cell, I have "Hello" (in bold, that's important)
in my second cell, I have "how are you" (not in bold)
I would like to concatenate the two texts into a third cell while keeping these formats to get:
"Hello how are you"
Right now, I can't do it. Even by making a macro, I can't figure out how to do it.
Thank you for your advice
Best regards,
Thierry
I have a concatenation issue with Excel.
An example: better than a long speech:
in my first cell, I have "Hello" (in bold, that's important)
in my second cell, I have "how are you" (not in bold)
I would like to concatenate the two texts into a third cell while keeping these formats to get:
"Hello how are you"
Right now, I can't do it. Even by making a macro, I can't figure out how to do it.
Thank you for your advice
Best regards,
Thierry
24 réponses
- 1
- 2
Suivant
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.
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.
@pijaku
Actually, I had this question just "now" (except for the format) and I have to say that banbar's answer perfectly solves my problem of concatenating several cells, since I don't need to keep any bold characters (I don't have any). So this topic revival, about 28 days before my "now" need, is pretty much a blessing ;-)
Actually, I had this question just "now" (except for the format) and I have to say that banbar's answer perfectly solves my problem of concatenating several cells, since I don't need to keep any bold characters (I don't have any). So this topic revival, about 28 days before my "now" need, is pretty much a blessing ;-)
For those who might later use this formula. Here is my solution that I find simpler than VBA for what I wanted to do.
What I wanted to do: concatenation while keeping the formatting of a % and text.
Solution (example): =TEXT(B2/B5,"0.00%")&" Potatoes"
B2 and B5 contained what we commonly call numbers!
What I wanted to do: concatenation while keeping the formatting of a % and text.
Solution (example): =TEXT(B2/B5,"0.00%")&" Potatoes"
B2 and B5 contained what we commonly call numbers!
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
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
Hello,
Well… this post is a bit old but I am facing the same issue.
My goal is also to concatenate cells that can be bold or not, depending on a date variable.
The solution I am considering is to create a VBA function "conservformat" that will allow me to keep the format of the source cell as in the following example:
=CONCATENATE(A1;conservformat(A2);A3)
Thus, the format of cell A2 will be preserved in my destination cell.
Here’s the beginning of my code:
Function ConservFormat(rngCells As Range) As Double
Application.Volatile
Dim cell As Range
ConservFormat = ???
On Error Resume Next
For Each cell In rngCells
If cell.Font.Bold Then ConservFormat = ???
Next cell
End Function
As you can see, the idea is to detect which cells are bold to set a format condition in the function. But I’m missing the format condition…
I’m not sure if I’m being clear, but if you understand my problem, do you have any idea how to do this?
Thank you!
Well… this post is a bit old but I am facing the same issue.
My goal is also to concatenate cells that can be bold or not, depending on a date variable.
The solution I am considering is to create a VBA function "conservformat" that will allow me to keep the format of the source cell as in the following example:
=CONCATENATE(A1;conservformat(A2);A3)
Thus, the format of cell A2 will be preserved in my destination cell.
Here’s the beginning of my code:
Function ConservFormat(rngCells As Range) As Double
Application.Volatile
Dim cell As Range
ConservFormat = ???
On Error Resume Next
For Each cell In rngCells
If cell.Font.Bold Then ConservFormat = ???
Next cell
End Function
As you can see, the idea is to detect which cells are bold to set a format condition in the function. But I’m missing the format condition…
I’m not sure if I’m being clear, but if you understand my problem, do you have any idea how to do this?
Thank you!
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....
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.
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.
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.
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
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.
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
thank you.
see you!
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.
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
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
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:
eric
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
To complete the difficulties encountered with the function, the text attributes applied to the text of a cell (and not to the cell itself) are lost as soon as it is placed in a VBA variable. They can only be manipulated within the cell itself.
If you find a trick to manipulate them directly in VBA, I’m all ears; all the issues would be resolved, but I haven't found one.
Now we need to try with separate functions like in your example, I hadn’t thought of that approach. To be tested...
eric
If you find a trick to manipulate them directly in VBA, I’m all ears; all the issues would be resolved, but I haven't found one.
Now we need to try with separate functions like in your example, I hadn’t thought of that approach. To be tested...
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
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
Hi Thierry,
are you sure you've read the replies properly?
You open the attached file in post 10, you open your file, you select the cells in your file that contain the concatenation formulas, and you run the macro, and in the adjacent cells you find the formatted string with bold, colors, etc.
And if you open this one: http://www.cijoint.fr/cij77852548227558.xls
you can even choose to overwrite your formulas (offset 0) or to paste the result 20 columns further if you want to preserve your occupied cells.
Eric
are you sure you've read the replies properly?
You open the attached file in post 10, you open your file, you select the cells in your file that contain the concatenation formulas, and you run the macro, and in the adjacent cells you find the formatted string with bold, colors, etc.
And if you open this one: http://www.cijoint.fr/cij77852548227558.xls
you can even choose to overwrite your formulas (offset 0) or to paste the result 20 columns further if you want to preserve your occupied cells.
Eric
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
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
(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
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 -_-
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 -_-
- 1
- 2
Suivant