Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have the following formula in Excel:

=IF(A5="File Start","File Creation DateTime = "&MID(C5,113,13),IF(A5="Start","Statement Date = "&MID(C5,65,8)&" | Opening Balance Date = "&MID(C5,88,8),IF(A5="Statement","Statement Date = "&MID(C5,65,8)&" | Value Date = "&MID(C5,108,8)&" | Booking Date = "&MID(C5,116,8),IF(A5="End","Statement Date = "&MID(C5,65,8)&" | Closing Balance Date = "&MID(C5,88,8),""))))  

Which gives a result along the lines of:

Statement Date = 20110217 | Value Date = 20101126 | Booking Date = 20110218

What I would like it to look like is:

Statement Date = 20110217 | Value Date = 20101126| Booking Date = 20110218

or

Statement Date = 20110217 | Value Date = 20101126 | Booking Date = 20110218

Where the bold items in option 2 are different colours.

share|improve this question
    
Unfortunately, I don't think this is possible... –  Sux2Lose Apr 8 '11 at 15:16

1 Answer 1

I don't think you can modify your formula to change the font. However, if you can accept copying and pasting the formula result into a new cell, then you can manipulate the text in that cell with VBA.

The code below copies your string, "Statement Date = 20110217 | Value Date = 20101126 | Booking Date = 20110218" from cell A5, then pastes it into B5. (You might need to "paste special/values".) It then inspects the string in B5, looking for the starting position of the numeric dates, which I assumed will always be 8 digits. Finally, it "bolds" all the numerals in the string, which was your first option for reformatting the string.

ChangeNumberFontInStringToBold()
'1.  Copy & paste the string to a new cell.
Range("A5").Select
Selection.Copy
Range("B5").Select
ActiveSheet.Paste

'2.  Find the start position of the numerics; load into array
Range("B5").Select

mytext = Range("b5").Text
myLength = Len(mytext)

Dim mystart() As Integer        'array to hold the starting positions of the numerics
Dim j
j = 0


For i = 1 To myLength
    myChar = Mid(mytext, i, 1)
    If IsNumeric(myChar) Then
        ReDim Preserve mystart(j)
        mystart(j) = i
        j = j + 1
        i = i + 10
    End If
Next i

'3.  Bold all the numerics
For k = 0 To UBound(mystart)
    With ActiveCell.Characters(Start:=mystart(k), Length:=8).Font
        .Name = "Arial"
        .FontStyle = "Bold"
    End With
Next k

'4. Park the cursor elsewhere
Range("C5").Select

End Sub

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.