excel formula formatting

Question 1

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.

Question 2

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

Leave a Reply Cancel reply