CODEWITHSUNDEEP
excelvba
Anthony14
Anthony14

Reputation: 105

How cut a substring?

I have a lot of string which can contain italic font. I want to copy this string with this font. In each new string I have bold word

Example: enter image description here

BIG STRING: enter image description here

I tried: 

Public Function GetDefinition(ByVal rngText As Range) As String
Dim theCell As Range
Set theCell = rngText.Cells(1, 1)

For I = 1 To Len(theCell.Value)
    If theCell.Characters(I, 1).Font.Bold = False Then
        If theCell.Characters(I + 1, 1).Text = " " Then
            theChar = theCell.Characters(I, 1).Text
            Else
            theChar = theCell.Characters(I, 1).Text
        End If
        Results = Results & theChar
    End If
Next I
GetDefinition = Results
End Function

Upvotes: 1

Views: 48

Answers (2)

Error 1004
Error 1004

Reputation: 8220

I think you could use this:

Option Explicit

Sub test()

    Dim LastRow As Long, i As Long, j As Long, PositionOfDot As Long

    With ThisWorkbook.Worksheets("Sheet1")

        'Find last row of column A
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        'Loop from row 1 to lastrow
        For i = 1 To LastRow
            'Copy paste from column A to C keeping formatting
            .Range("A" & i).Copy .Range("C" & i)
            'Find the position of "."
            PositionOfDot = InStr(1, .Range("A" & i), ".")
            'Delete characters starting from the first one up to PositionOfDot+1
            .Range("C" & i).Characters(1, PositionOfDot + 1).Delete

        Next i

    End With

End Sub

Results:

enter image description here

Upvotes: 1

Damian
Damian

Reputation: 5174

If your bold string always ends with a dot this will do it for you:

Option Explicit
Public Function GetDefinition(ByVal rngText As Range) As String

    Dim SplitBold As Variant

    SplitBold = Split(rngText, ". ")
    GetDefinition = Trim(SplitBold(1))

End Function

Upvotes: 0

Related Questions