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.

 

[pgc01]

Hi Titian

I have several questions about you post:

- can your string have more than one set of initials? In that case what is the answer you want?

- can the same set of initials appear more that once? In that case what to do.

- can the set of initials appear inside a word like SAW?

For this simplest case of just one set of initials that always appears isolated, try, for ex.:

=LOOKUP("z",IF({1,0},"",LOOKUP(9,MATCH("*"&{"AW","PW","TN","NR"}&"*",$L2,0),{"AW","PW","TN","NR"})))

 

[pgc01]

pgc01
From Excel's Help:

A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change. This method has no effect if it's not inside a user-defined function used to calculate a worksheet cell.

Any questions? I do always include this statement in case user wouldn't say: "Hey, I change text, but formula did nothing. You formula doesn't work!". To avoid such statements, I mark UDF as volatile.

In this case you want to recalculate your udf when the input changes. This is already automatic. Using Application.Volatile in this case makes the udf inefficient and does not give you any benefit.

 

[Sektor]

pgc, It's was in one of my UDF, so I copied it and didn't remove it. Yes, in this case it absolutely necessary. It has no dependencies on non-incoming parameters.

 

[Titian]

Thank you Pgc01, your formula works well on my present data set.

There are instances where there may be e.g. AW/TN. It is returning "TN" in this example. Given the reason why I am extracting the initials I am not concerned as to which pair of characters are returned.

The same set of initials ought not to appear more than once, however, given that it is a free format field the user may input them twice; if so return one set.

It is possible that the initials could appear within a word, "SAW" in your example, in which case they ought to be disregarded.

I get the gist of your formula but attempting to learn more by using "evaluate formula" gives little help as it jumps to the answer in just a couple of steps. What I was particularly interested in was the 9 in "LOOKUP(9,MATCH..."

Thanks again.

 

[pgc01]

It is possible that the initials could appear within a word, "SAW" in your example, in which case they ought to be disregarded.

Hi Titian

In that case can you tell what will be the delimiters?
Can we say, for ex., that the sets of letters will always be delimited by a space or a slash ("/")?

I get the gist of your formula but attempting to learn more by using "evaluate formula" gives little help as it jumps to the answer in just a couple of steps. What I was particularly interested in was the 9 in "LOOKUP(9,MATCH..."

You are doing a match using an array with 4 elements and so the result will always be either a number 1-4 or an error value.

If you use a lookup value bigger than any possible value in the lookup array, the Lookup() function will return the value corresponding to the last non-error value in the array.

In this case, I'm using 9, but I could use any number bigger than 4.

 

[Titian]

Hi Pgc01,

Yes, we can say that the sets of letters will always be delimited by a space or a slash ("/").

Thanks for your explanation.

Regards

 

[pgc01]

Titian

I tweaked the formula to take into account the delimiters.

=LOOKUP("z",IF({1,0},"",LOOKUP(9,MATCH("* "&{"AW","PW","TN","NR"}&" *",SUBSTITUTE(" "&$L2&{" "},"/"," "),0),{"AW","PW","TN","NR"})))

Please test.

 

[Titian]

Hi Pgc01,

Works fine on my data set as amended for testing purposes.

Thanks a lot, I would never have got there!

Regards

 

[pgc01]

I'm glad it helped. Cheers!