Concatenation and format in Excel
Solved
Luana33
Posted messages
13
Status
Member
-
Luana33 Posted messages 13 Status Member -
Luana33 Posted messages 13 Status Member -
Hello,
I’m having a small issue in Excel.
I would like to concatenate the values of 3 cells into one, while preserving the original formatting of the base cells, in particular the font color.
In other words, my first cell contains red text, the second green, and the last yellow.
I would like to create a cell that takes the values from these 3 cells but also preserves the font color of each of them.
Thank you very much for your help,
Lou
I’m having a small issue in Excel.
I would like to concatenate the values of 3 cells into one, while preserving the original formatting of the base cells, in particular the font color.
In other words, my first cell contains red text, the second green, and the last yellow.
I would like to create a cell that takes the values from these 3 cells but also preserves the font color of each of them.
Thank you very much for your help,
Lou
17 answers
It’s not very reassuring: "Rigorously impossible"??? Really?
Yet, at first glance it seems relatively simple; I’m just trying to add to the value-concatenation function an option that also preserves the format of the original cells...
Doing it manually is impossible, I have more than 300 tables of 300 cells each...
Is there another software that could do this?
Yet, at first glance it seems relatively simple; I’m just trying to add to the value-concatenation function an option that also preserves the format of the original cells...
Doing it manually is impossible, I have more than 300 tables of 300 cells each...
Is there another software that could do this?
Hello,
This seems possible in Excel, but we’ll have to go through a VBA function.
You say...
--
Best regards,
Franck P
This seems possible in Excel, but we’ll have to go through a VBA function.
You say...
--
Best regards,
Franck P
Hello and thank you for the prompt response,
it’s reassuring but my VBA skills are very limited...
I’ll still try by looking around. But if someone has already encountered this problem and solved it, I’d be delighted to discuss to understand the approach...
Thank you very much,
it’s reassuring but my VBA skills are very limited...
I’ll still try by looking around. But if someone has already encountered this problem and solved it, I’d be delighted to discuss to understand the approach...
Thank you very much,
Apparently the problem is complex, and to deal with it my VBA skills are extremely weak. I admit that I’m really struggling...
I checked other posts on the subject and someone had provided a VBA solution on a specific site but it’s no longer available...
In short, I don’t really know how to proceed...
No one knows where I could look for guidance on the issue or point me to another software to handle it?
Thank you very much in advance...
I checked other posts on the subject and someone had provided a VBA solution on a specific site but it’s no longer available...
In short, I don’t really know how to proceed...
No one knows where I could look for guidance on the issue or point me to another software to handle it?
Thank you very much in advance...
Hello,
First you should know that what you request is not achievable by a custom function; it can only be a procedure called on a case-by-case basis or on an event (cell modification, sheet activation, for example)
Here is a procedure I had implemented some time ago.
The example file seems complex but that's because there are several usage examples in it.
To test :
The concatenation formulas are in H3:H8, you select this range and run the RecupFormatCel procedure, that's all.
I recover bold, underline, color
It acts on the selected range containing the concatenation formulas and you can choose to place the concatenation wherever you want relative to the formula thanks to the offset (0 overwrites the formula).
The best is to set an offset of 1 and to hide the column with the formulas. Thus they remain editable.
I also added the possibility to insert line breaks with the code "vbLf" inserted into the formula
http://www.cijoint.fr/cjlink.php?file=cj201107/cij2AlrPwi.xls
See if it works for you and feel free to ask questions, I'm not sure I explained it well ;-)
eric
First you should know that what you request is not achievable by a custom function; it can only be a procedure called on a case-by-case basis or on an event (cell modification, sheet activation, for example)
Here is a procedure I had implemented some time ago.
The example file seems complex but that's because there are several usage examples in it.
To test :
The concatenation formulas are in H3:H8, you select this range and run the RecupFormatCel procedure, that's all.
I recover bold, underline, color
It acts on the selected range containing the concatenation formulas and you can choose to place the concatenation wherever you want relative to the formula thanks to the offset (0 overwrites the formula).
The best is to set an offset of 1 and to hide the column with the formulas. Thus they remain editable.
I also added the possibility to insert line breaks with the code "vbLf" inserted into the formula
http://www.cijoint.fr/cjlink.php?file=cj201107/cij2AlrPwi.xls
See if it works for you and feel free to ask questions, I'm not sure I explained it well ;-)
eric
Thank you a thousand times for your reply...
It's really great, I just have a few questions...
As for the explanation, it should be very good but my limited VBA skills make it a bit hard to understand...
So if I recap the way I understood things...
The macro uses the concatenated formula to retrieve the format of the base cells, is that it?
The file works really well but I have just a few issues adapting it: how to get the macro to adapt to my tables?, do I necessarily have to, like you, specify in the macro the cells containing the base concatenated formula and the destination cells? Or can I simply select them to activate the macro and get the result?
Moreover, I don’t quite understand the starting idea... What you mean is that I am obliged to create a command and that I cannot directly type a simple formula, is that it?
Thanks again, really thanks...
It's really great, I just have a few questions...
As for the explanation, it should be very good but my limited VBA skills make it a bit hard to understand...
So if I recap the way I understood things...
The macro uses the concatenated formula to retrieve the format of the base cells, is that it?
The file works really well but I have just a few issues adapting it: how to get the macro to adapt to my tables?, do I necessarily have to, like you, specify in the macro the cells containing the base concatenated formula and the destination cells? Or can I simply select them to activate the macro and get the result?
Moreover, I don’t quite understand the starting idea... What you mean is that I am obliged to create a command and that I cannot directly type a simple formula, is that it?
Thanks again, really thanks...
Hello,
You must copy its code (copy-paste) into your file and adapt it to the area you want to process. But I just looked at its code (Alt-F11), you need to adapt it to your case!!!
To run the macro, Tools > Macro...
If you really insist, replace "Sub RecupFormatCel()" with "Function RecupFormatCel()" and the macro becomes a function you can reuse in a cell, like "=Recupformatcel()"
Do you understand me?
See you later
You must copy its code (copy-paste) into your file and adapt it to the area you want to process. But I just looked at its code (Alt-F11), you need to adapt it to your case!!!
To run the macro, Tools > Macro...
If you really insist, replace "Sub RecupFormatCel()" with "Function RecupFormatCel()" and the macro becomes a function you can reuse in a cell, like "=Recupformatcel()"
Do you understand me?
See you later
Re,
Mea culpa. You are right Eric. So the macro remains to run on a selected selection before calling it.
Have a good evening.
Mea culpa. You are right Eric. So the macro remains to run on a selected selection before calling it.
Have a good evening.
Hi Er,
Thank you for this intervention Sylvain, I suppose...
Dear Eric,
Attached is an excerpt from one of my Excel workbooks with at the bottom the simple concatenation performed (without the font colors)
http://www.cijoint.fr/cjlink.php?file=cj201107/cijhSxDBHS.xls.
Thanks in advance for your help...
Have a good evening or good night,
Luana.
Thank you for this intervention Sylvain, I suppose...
Dear Eric,
Attached is an excerpt from one of my Excel workbooks with at the bottom the simple concatenation performed (without the font colors)
http://www.cijoint.fr/cjlink.php?file=cj201107/cijhSxDBHS.xls.
Thanks in advance for your help...
Have a good evening or good night,
Luana.
Sorry, I may have forgotten to specify the actions I want to see that trigger the macro.
It would be great if it could trigger at the moment the sheet in question is opened.
Thanks again...
Luana.
It would be great if it could trigger at the moment the sheet in question is opened.
Thanks again...
Luana.
Hello,
First of all, confirm to me that your colors are applied manually and not by conditional formatting.
Also, we might be able to simplify your work...
- If each of the 3 number tables always generates P, M and A in that order, we can retrieve the colors directly from the numbers and skip the intermediate tables.
- If there is a rule for the colors of the numbers, we could automate that part.
So if you want, please explain as clearly as possible how you build your table and also whether the tables can vary in size and/or position, and whether there are always 3 of them, and we’ll see what can be done.
eric
edit:
It would be great if it could trigger when the workbook is opened.
Okay, but you mentioned several sheets.
The names of the relevant sheets should have a trait that the others do not. For example, their name starts with a D.
Hi there,
Yes, the colors are conditional, but I addressed this issue using SAS, so I export the results to Excel and the values are already colored when they arrive in Excel. I think this is equivalent to manual...
Regarding the final table, it will indeed always include the three letters PMA, which I hope can each be colored using the color from each of the three initial tables (the ones containing the values if possible).
On each sheet, there are only three tables that I will arrange in the same way to facilitate the operation.
You’re right, I have several sheets—about 7 per workbook, each containing three tables—and for each of them I want to perform the same concatenation operation.
Okay for the D if it’s necessary...
I hope I’ve been clear enough; if any information is missing, I’m fully available.
Thank you...
Hi,
It shouldn’t be too far from the goal, it only works on manual colors. All the sheets whose name starts with D are processed when activated.
When opening the file the result is empty. Select Sheet2, then Data1, the result appears. This will have to be done at every refresh if you have modified the colors.
In the init module I grouped the essential variables to modify if your arrays evolve a little (count/size/position).
As a bonus, on selecting a result the corresponding data cells are highlighted.
http://www.cijoint.fr/cjlink.php?file=cj201107/cijd7j8Mot.xls
Test thoroughly, it’s much harder to come back to it 4 days later...
One thought: in the absence of a value, wouldn’t it be clearer to keep the letter in white ink? They would have kept their position at all times (and the work would be easier.. ;-) )
eric
It shouldn’t be too far from the goal, it only works on manual colors. All the sheets whose name starts with D are processed when activated.
When opening the file the result is empty. Select Sheet2, then Data1, the result appears. This will have to be done at every refresh if you have modified the colors.
In the init module I grouped the essential variables to modify if your arrays evolve a little (count/size/position).
As a bonus, on selecting a result the corresponding data cells are highlighted.
http://www.cijoint.fr/cjlink.php?file=cj201107/cijd7j8Mot.xls
Test thoroughly, it’s much harder to come back to it 4 days later...
One thought: in the absence of a value, wouldn’t it be clearer to keep the letter in white ink? They would have kept their position at all times (and the work would be easier.. ;-) )
eric
This is really great, you're a genius, it works super well...
I tested it on 3 different folders and it's perfect...
I who was totally depressed after the "rigorously impossible" and the "headache for the user"...
I have a proposal for you, wouldn't you like to work in research? I believe I could quite easily find something for you...
Really thanks, you’re saving my life...
I owe you a line of thanks...
I tested it on 3 different folders and it's perfect...
I who was totally depressed after the "rigorously impossible" and the "headache for the user"...
I have a proposal for you, wouldn't you like to work in research? I believe I could quite easily find something for you...
Really thanks, you’re saving my life...
I owe you a line of thanks...
Just for reflection,
Yes, indeed, you’re right...
Moreover in these cells, I normally have values of 1, I could have left them blank directly under SAS...
Sorry, originally I thought it would be much easier than that... :-))
Yes, indeed, you’re right...
Moreover in these cells, I normally have values of 1, I could have left them blank directly under SAS...
Sorry, originally I thought it would be much easier than that... :-))
Here’s the translation:
This is what it looks like with PMA all the time but white ink on the missing values.
If you’re the one removing the 1, you can easily replace the test ="" with =1
Don’t forget to mark as resolved when the time comes
http://www.cijoint.fr/cjlink.php?file=cj201107/cijYRWpggn.xls
eric
If you’re the one removing the 1, you can easily replace the test ="" with =1
Don’t forget to mark as resolved when the time comes
http://www.cijoint.fr/cjlink.php?file=cj201107/cijYRWpggn.xls
eric
Hello Lou.
As you can see, the important work carried out by eriiic required the use of VBA language to write macros.
You therefore understand why at your request "I’m having a small problem in Excel. I would like to concatenate the values of 3 cells into one while preserving the initial formatting of the cells" I spontaneously replied "Out of the question. Absolutely impossible," because indeed classic Excel formulas, functions and commands do not allow it.
And I admit that I thought, even with macros, we wouldn’t be able to do it!
Best regards.
--
Nice retirement! Especially in the Caribbean ... :-)
☻ Raymond ♂
As you can see, the important work carried out by eriiic required the use of VBA language to write macros.
You therefore understand why at your request "I’m having a small problem in Excel. I would like to concatenate the values of 3 cells into one while preserving the initial formatting of the cells" I spontaneously replied "Out of the question. Absolutely impossible," because indeed classic Excel formulas, functions and commands do not allow it.
And I admit that I thought, even with macros, we wouldn’t be able to do it!
Best regards.
--
Nice retirement! Especially in the Caribbean ... :-)
☻ Raymond ♂
Okay then, thank you Raymond for your reply...
Simply I don’t think it’s necessary to be so categorical when we don’t know the procedures to carry out an action.
In this case, it’s simply possible to say: I know that you cannot perform this action via the classic Excel functions and I don’t think it’s possible to accomplish it using macros.
C is always better than an absolute no, strictly impossible...
theoretically, anything is possible...
Anyway, thanks a lot anyway for having looked into my problem...
Have a good evening...
Simply I don’t think it’s necessary to be so categorical when we don’t know the procedures to carry out an action.
In this case, it’s simply possible to say: I know that you cannot perform this action via the classic Excel functions and I don’t think it’s possible to accomplish it using macros.
C is always better than an absolute no, strictly impossible...
theoretically, anything is possible...
Anyway, thanks a lot anyway for having looked into my problem...
Have a good evening...
But it would no longer be simply a Office software, but a language of Programming, which would force you to change your Forum.
You're absolutely right.
Moreover, even with VBA, it's doable, but with so many conditions to meet that it becomes a headache for the user...