Extract initials from a text string

[Titian]

I have a text string in column L. I have four sets of initials AW, TN, PW, NR which may be embedded in that string which I would like to exctract into column S.

I would like a formula solution to do this and have been playing around with variations of e.g.

=IF(SUM(ISNUMBER(SEARCH({"AW","PW","TN","NR"},$L2))+0),{"AW","PW","TN","NR"})

which clearly doesn't work.

Any help appreciated as usual.

 

[Sektor]

UDF.

[COLOR="Blue"]Function[/COLOR] GetInitials(Str [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    Application.Volatile
    [COLOR="Blue"]With[/COLOR] CreateObject("VBScript.RegExp")
        .Pattern = "[A-Z]{2}"
        GetInitials = .Execute(Str)(0)
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]

 

[Titian]

Thanks Sektor, however your UDF appears to get the first two characters of the string not the specific initials required.

Another point worth noting and why I would like a formula solution is that the file I am working with has been created by a macro. So any solution will have to be incorporated into that macro.

Regards

 

[Sektor]

Corrected.

[COLOR="Blue"]Function[/COLOR] GetInitials(Str [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    Application.Volatile
    [COLOR="Blue"]With[/COLOR] CreateObject("VBScript.RegExp")
        .Pattern = "(AW|PW|TN|NR)"
        GetInitials = .Execute(Str)(0)
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]

 

[Titian]

We now have the required initials extracted, thanks.

There is an error value returned in instances where none of the initials are present in the string, can the code return "" in those instances?

To use this solution I would need to incorporate the UDF in my macro - which is distributed to other users in the office, some with Excel 2007, others on 2003 - how do I do that?

Thanks for yor help.

 

[Titian]

Just found that I can wrap the UDF in "=IFERROR(..." to overcome the error returned.

I would still be interested in your suggestions for distributing your UDF.

Thanks.

 

[Sektor]

Corrected. No need for IFERROR. To port code, you just copy the module with this UDF into VBA project of another workbook by dragging it in VBE and dropping to target workbook's VBA project or by Exporting and then Importing.

[COLOR="Blue"]Function[/COLOR] GetInitials(Str [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    Application.Volatile
    [COLOR="Blue"]Dim[/COLOR] mc [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Object[/COLOR]
    [COLOR="Blue"]With[/COLOR] CreateObject("VBScript.RegExp")
        .Pattern = "(AW|PW|TN|NR)"
        [COLOR="Blue"]Set[/COLOR] mc = .Execute(Str)
        [COLOR="Blue"]If[/COLOR] mc.Count = 0 [COLOR="Blue"]Then[/COLOR] GetInitials = "" [COLOR="Blue"]Else[/COLOR] GetInitials = mc(0)
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]

 

[Titian]

Thanks Sektor that works fine.

If anyone else can provide a formula solution I would be interested as incorporation into the macro is so much simpler.

Thanks for your interest.

 

[pgc01]

[COLOR=blue]Function[/COLOR] GetInitials(Str [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]) [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]
    Application.Volatile
...
[COLOR=blue]End[/COLOR] [COLOR=blue]Function[/COLOR]

Hi Sektor

One usually only uses Application.Volatile when absolutely necessary. Why are you using it in this case?

 

[Sektor]

N/A