Returning a Text string from =CONCAT, only show value?

Copper Contributor

In my org, I we needed to generate HTML code for email buttons. So that people wouldn't need to code, and so that I wouldn't need to keep writing new code for them, I created a simple HTML generator in Excel that allows users to choose colors and type in text, and then I use various =IF functions to replace choices with correct coding and =CONCAT to put together the complete string of code. I then password protect everything but the cells they need to input their choices.

 

It works great, except for one thing.

 

The final cell containing the =CONCAT formula returns the code completely correctly. However, if the user copies this cell directly, when they paste it somewhere to use it, extra " are inserted.

 

ie: <p style="text-align:center;"> becomes "<p style=""text-align:center;"">"

 

And obviously you can't click into the cell to copy the text string, because it will just show the formula.

 

A workaround is to copy the cell and paste-as-value into another cell, then select that cell and copy the text from within. 

 

I'd like a way to bypass this copy and paste step so that I can have more straightforward protection on the workbook, and to reduce opportunities for operator error. This is intended to be used by users with limited Excel and coding knowledge, so the more straightforward the better. I know whatever I end up with I will need to make sure they copy the text from inside the cell and not the cell itself to avoid the extra ".

 

Is there a function, or other feature I'm not aware of, that will return the contents of A20 (the value of the function) as just text? Or is paste-as-value the only option?

 

CKurtz_3-1682518506514.png

 

The code itself for anyone who is curious. Open to suggestions to optimize the [if mso] condition, as this is my first time coding for Outlook.

 

 

<p style="text-align: center;"><a style="background-color: #2d5980; font-size: 18px; font-family: Helvetica, Arial, sans-serif; font-weight: bold; text-decoration: none; padding: 14px 20px; color: #ffffff; border-radius: 5px; display: inline-block; mso-padding-alt: 0;" href="URL GOES HERE" target="_blank" rel="noopener"> <!-- [if mso]>
    <i style="letter-spacing: 25px; mso-font-width: -100%; mso-text-raise: 30pt;">&nbsp;</i><i style="letter-spacing: 25px; mso-font-width: -100%; mso-text-raise: 30pt;">&nbsp;</i>  <![endif]--> <span style="mso-text-raise: 15pt;">Buy Now</span> <!-- [if mso]>    <i style="letter-spacing: 25px; mso-font-width: -100%;">&nbsp;</i> <![endif]--> </a></p>

 

 

 

2 Replies

@CKurtz You could use a simple macro like this one:

 

Sub copytext()
    Dim dataObj As DataObject
    Set dataObj = New DataObject
    With dataObj
        .SetText Range("A20").Text
        .PutInClipboard
    End With
End Sub

 

It requires a reference to the MSForms 2.0 library, which can easily be added by inserting a userform in your VBA project (and removing it again)

Interesting!

Admittedly, I have never used macros. I guess now is as good a time as any to learn!