Excel - Concatenate

[TheBramble]

I'm using CONCATENATE with text and cells and what to embolden part of the output.

Are there any switches or other methods for formatting of this nature?

 

[zzaxx99]

Don't think you can do this with a formula, though you could do it with a macro - a quick bit of Googling reveals this which looks like it would do what you want.

Depending what you were trying to do, you could either hang the macro off a keystroke, a button, a menu, or attach it to a timer to run periodically and automatically

 

[zzaxx99]

It's a shame that Excel doesn't have rather better format controls for stuff like this - eg include ^b for bold in the text or somesuch.

I suppose another way to do this, to avoid the macro route, would be to declare a function, so that your formula would be =FancyFormat(concatenate(....)), and then you could use formatting characters in your string, and have the function interpret them.

 

[TheBramble]

Thanks Steve.

The macro doesn't look like it'll give me waht I need.

Totally agree something simple like a ^b or /b text format character would be a really usefull addition.

 

[zzaxx99]

Lateral thinking idea: if you're concatenating (say) 3 field each time, could you just use 3 narrow columns next to each other, then colour each column individually?

 

[TheBramble]

I'm all for lateral and low-tech, but what I'm producing is largely text and the '3 consecutive columns' bit would really constrain my artisitic flair!

 

[zzaxx99]

Use the concatenate to include HTML code into the string, output it as a .HTM file and use a web browser to view it?

 

[FetteredChinos]

tony, are you trying to format only PART of the cell, or the entire cell?

i'll see what i can come up with.

FC

 

[swandro]

I know how you can do this. But I would just like to understand a little better what you want to do. Can you give us an example? How will the function know which part of the resulting text has to be made bold?

Cheers

Rob

 

[TheBramble]

tony, are you trying to format only PART of the cell, or the entire cell?

Example:-

CONCATENATE("52wk HIGH Breached on "&X1&" at "&X2&" Level "&X3)

And I want to embolden the X3 cell - for instance.

How will the function know which part of the resulting text has to be made bold?

I always want the same cellref of the output to be emboldened.

 

[zzaxx99]

Depending on what you do with the output, I'm inclined to think that embedding HTML codes is the way to go. In your example, this would mean:

CONCATENATE("52wk HIGH Breached on "&X1&" at "&X2&" Level <b>"&X3,"</b>")

Which would embolden the value of the X3 cell, provided that your "output" is a file and you view it with a web browser

What is the format of your desired output? When you say you're largely outputting text, is this as a separate text report, or as text within your Excel spreadsheet?

If the former, the HTML approach would work; if the latter, another alternative, if we can't format part of the results of a formula (and I suspect we can't), would be to:

1. have another results page
2. have a macro which copied the values of your output to the result page
3. same/another macro applies formatting to the results

 

[TheBramble]

That's clinched it - I'll use an output text file with html codes embedded and view via browser.

It was just displaying it within the excel sheet.

Thanks all for your help & assistance.

 

[swandro]

Since I had been looking at another way of doing this, I might as well post my solution. This approach is designed to work real-time, i.e. it will automatically update your resulting text whenever the value in X3 changes.

I have included a spreadsheet that demonstrates it. To see it work, change the value in H3. To see the code, go into Tools , VBA.. There are 2 bits of code, the MakeBold subroutine and a bit of code in the ThisWorkBook section.

This is what happens. It does the concatentation in cell A2. The value in A2 is then copied and pasted into A4. Then it looks for the text following the last "L" in cell A4 and makes it bold.

If you have trouble running th procedures, you may have to set Macro Security to low.

Cheers

Rob