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 =
Statement Date = 20110217 | Value Date = 20101126 | Booking Date = 20110218
Where the bold items in option 2 are different colours.
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