embed text attribute in formula

[ccrb]

The field is a concatenation of three other fields and only the first of three parts should be boldfaced -- the second and third part should be normal faced.

Is there an easy way to do this in excel 2007? Right now, the formula is:

=CONCATENATE(UPPER(TRIM('Raw Data'!H26)),CHAR(10), TRIM('Raw Data'!G26)," [",TRIM('Raw Data'!J26),"]")

and the part in red, is to appear boldfaced.

any help?

 

[pgc01]

Hi ccrb
Welcome to the board

No, that's not possible with a formula. The text resulting from a formula has all the same format.

To do what you want you'd have to use vba and replace the formula with vba code.

 

[ccrb]

how difficult is a vba formula? You can see the excel formula in the original posting.

 

[pgc01]

how difficult is a vba formula? You can see the excel formula in the original posting.

It's not a vba formula its vba code. You have to write vba code that executed produces the same result as the formula, and then write the result in the cell.

To make it automatic you must tie the execution of this code to a change in any of the input cells. You can do it using a worksheet event.

I can write an example for you, but if you don't know vba you won't be able to adapt it or reproduce it for other formulas.

If you want an example post the address of the cell where you'd want the output.

Remark: if you opt for a vba solution, remember that some companies don't allow macro execution in the spreadsheets and so the spreadsheet may be unusable by others.

 

[ccrb]

This is a problem in a proprietary worksheet. No problems using vba code. I do know basic VB (ie VB 101 type stuff).

It's not a vba formula its vba code. You have to write vba code that executed produces the same result as the formula, and then write the result in the cell.

To make it automatic you must tie the execution of this code to a change in any of the input cells. You can do it using a worksheet event.

I can write an example for you, but if you don't know vba you won't be able to adapt it or reproduce it for other formulas.

If you want an example post the address of the cell where you'd want the output.

Remark: if you opt for a vba solution, remember that some companies don't allow macro execution in the spreadsheets and so the spreadsheet may be unusable by others.

 

[pgc01]

Ok. Since you didn't answer my question about where you apply the formula I'll assume the following scenario:

You have a workbook with 2 worksheets, Sheet1 and "Raw Data".

This solution simulates the formula in Sheet1!A1:

=CONCATENATE(UPPER(TRIM('Raw Data'!H26)),CHAR(10), TRIM('Raw Data'!G26)," [",TRIM('Raw Data'!J26),"]")

where the red part is to be bolded.

Do the following:

- activate the worksheet "Raw Data"
- right-click on the worksheet's tab and choose "View Code". You'll be taken to the "Raw Data" module in the VBEditor.
You can also go to the VBEditor and activate the module "Raw Data"
- paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim s As String, l As Long
' if any of the cells H26, G26, J26 was modified
If Not Intersect(Target, Range("H26,G26,J26")) Is Nothing Then
    s = UCase(Trim(Range("H26")))
    l = Len(s)
    s = s & vbLf & Trim(Range("G26")) & "[" & Trim(Range("J26")) & "]"
 
    ' writes the result in Sheet1!A1 and bolds the first part
    With Worksheets("Sheet1").Range("A1")
        .Value = s
        .Characters(1, l).Font.Bold = True
    End With
End If
End Sub

- go back to the "Raw Data" worksheet, change G26 or H26 or J26 and check the result in Sheet1!A1

HTH

 

[ccrb]

wow. that's a big help.
I didn't see the question. The destination sheet is "new formatted" and the destination cell is H26.

And your code works. Thank you. With the changes to the code, so your code looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim s As String, l As Long
' if any of the cells H26, G26, J26 was modified

    s = UCase(Trim(Range("H26")))
    l = Len(s)
    s = s & vbLf & Trim(Range("G26")) & "[" & Trim(Range("J26")) & "]"
 
    ' writes the result in Sheet1!A1 and bolds the first part
    With Worksheets("new formatted").Range("H26")
        .Value = s
        .Characters(1, l).Font.Bold = True
    End With
 End Sub

now I need to do this for rows 2-27, so I'm presuming a for next loop, with the index being used as part of the different range statements. Not sure exactly how to code (Range("Gindex")) but I presume I'd create a string and the resulting code would look like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim s As String, l As Long, index As Integer, gstring As String, hstring As String, jstring As String
For index = 2 To 27
    gstring = "G" & index
    hstring = "H" & index
    jstring = "J" & index
        s = UCase(Trim(Range(hstring)))
        l = Len(s)
        s = s & vbLf & Trim(Range(gstring)) & "[" & Trim(Range(jstring)) & "]"
 
        ' writes the result in newformatted!Hxx and bolds the first part
        With Worksheets("new formatted").Range(hstring)
            .Value = s
            .Characters(1, l).Font.Bold = True
        End With

Next
end sub

It's working!!! THANKS

 

[pgc01]

Hi

I'm glad it worked.

Your code works, it's however very inefficient for 2 reasons:

1 - You removed the statement

If Not Intersect(Target, Range("H26,G26,J26")) Is Nothing Then

That's an important statement. The Change event is fired whenever a cell value is modified. If you don't include this statement it means that your code will be executed whenever any cell in the worksheet in changed, even if has nothing to do with this solution.

2 - You are looping.

You should not loop. When you change a cell in column H, G, J in rows 2:27 in the "raw format" worksheet you are only interested in changing the value of the cell in worksheet "new formatted" in column H in the same row. If you loop like you are doing you're calculating and assigning the values for all the other cells in H2:H27, which is not necessary. You know that the cell that was modified is the one in the Target range, so you can use it to get the row.

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim s As String, l As Long, index As Integer, gstring As String, hstring As String, jstring As String
 
' if any of the cells in column H, G, J in rows 2:27 was modified
If Not Intersect(Target, Range("H2:H27,G2:G27,J2:J27")) Is Nothing Then
    gstring = "G" & Target.Row
    hstring = "H" & Target.Row
    jstring = "J" & Target.Row
 
    s = UCase(Trim(Range(hstring)))
    l = Len(s)
    s = s & vbLf & Trim(Range(gstring)) & "[" & Trim(Range(jstring)) & "]"
 
    ' writes the result in newformatted!Hxx and bolds the first part
    With Worksheets("new formatted").Range(hstring)
        .Font.Bold = False
        .Value = s
        .Characters(1, l).Font.Bold = True
    End With
End If
End Sub

 

[ccrb]

Looping yes. However the nature of the worksheet "raw data" is that data is pasted in the rows all at once. There's no "try this" or "change that". The data is plopped in, then the resultant "new formatted" sheet is simply printed out as a pdf file and imported in a document.

Thanks again so much for your help. This is very good, very helpful.

 

[pgc01]

You are right. I thought this was the usual case where you change one cell at a time. In your case I see that you do need to update the whole range. I'm glad your problem is solved.

Cheers.