Format specific text in excel string into italics

  • Hello ozgrid community,


    I am trying to format specific text within an excel cell but found that I cannot do so without changing the cell formatting.



    I have cells with detail listing plant communities, these have the common name and the scientific name immediately after like so:


    "Hedge lining access track. Vegetation: hawthorn Crataegus monogyna (D), bramble Rubus fruticosus agg (F), hazel Corylus avellana (O)"



    The scientific names following the common name needs to be in italics and I need to complete this in multiple rows within a column, like so:


    "Hedge lining access track. Vegetation: hawthorn Crataegus monogyna (D), bramble Rubus fruticosus agg (F), hazel Corylus avellana (O)"



    The same species will feature frequently in these datasets.


    Would anybody be able to advise on creating a macro to complete this task?


    Many thanks in advance.

  • Is there any pattern to the text? In your example there is a one-word English name and then a two-word Latin name and something in brackets. Does that vary?


    This is probably the way to go, you just need a surefire way to identify the Latin names.
    [VBA]
    Sub x()


    Dim n1 As Long, s As String, v, w, z, i As Long


    v = Split(Range("A1"), ":")
    w = Split(v(1), ",")


    For i = LBound(w) To UBound(w)
    z = Split(Trim(w(i)), " ")
    s = z(1) & " " & z(2)
    n1 = InStr(Range("A1"), s)
    Range("A1").Characters(n1, Len(s)).Font.Italic = True
    Next i


    End Sub[/VBA]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!