Parse out letters

[jrRaid]

For a government document we need to parse out 4 letters from names.

1.The first letter of the last name.

This no problem.

2.If the first letter of the last name is a vowel, we need the first following vowel.

(f.i. lastname = Aguilar, 1st letter = A, 2d letter U )

3. If the first letter is a consonant, we need the first following vowel.

(f.i. lastname = Duarte, 1st letter = D, 2d letter U )

4. First letter of first name.

This is no problem.

So, 1 and 4 is no problem, but how to tackle 2 and 3.

TIA

Edited October 4, 2007 by Guest

[Vaughan]

Conditions 2 and 3 actually combine and kinda cancel each other out. No matter what, the second vowel is needed.

So it's really:

1. The first letter of the last name.

2. The first following vowel.

3. First letter of first name.

I'd suggest a recursive custom function would be needed.

[jrRaid]

You're right with the combined 2/3 approach.

My first idea was also a CF.

I browsed the Brian Dunning CF list up and down, tried some of the example that looked close to what I need, but no luck.

Hence my posting here.

Would it be better to move this post to the custom function section ?

[David Jondreau]

There's probably simpler ways of doing this, but I think it'll work...

NameCF(Name; Counter)

Let([

Pos = Case(Counter<2;2;Counter);

End = Length(Name);

Letter = Middle(Name;Pos;1) ;

ConvertName = Substitute(Name;["a";"1"];["e";"1"];["i";"1"];["o";"1"];["u";"1"]);

Letter1 = Middle(ConvertName;Pos;1)

];

Case(

Counter > End; "X";

Letter1=1; Letter;

NameCF(Name; Counter + 1)

))

You'll need to figure out how to handle the exceptions. Like the semivowels Y and W in English. Does Lord Byron get a Y or an O? And Senor Ayuda a Y or a U? And what happens to the guy with last name "Art"? Or to Mr. T? Right now, this functions will return an X for those situations.

[LaRetta]

Wouldn't something simpler like this work (and no CF needed)?

Let (

[

pos1 = Left ( LastName ; 1 ) ;

pos2 = Left ( Filter ( LastName ; "AEIOUaeiou" ) ; 1 ) ;

pos3 = Left ( FirstName ; 1 )

] ;

pos1 & pos2 & pos3

)

[comment]

I don't think you are following rule #2. Still, I agree that a recursive calculation is not necessary:

Let ( [

char1 = Left ( LastName ; 1 ) ;

rest = Right ( LastName ; Length ( LastName ) - 1 ) ;

char2 = Left ( Filter ( Upper ( rest ) ; "AEIOU" ) ; 1 ) ;

char3 = Left ( FirstName ; 1 )

] ;

char1 & char2 & char3

)

I share David's concerns regarding the viability of the scheme - but hey, it's for the government...

[LaRetta]

Ah. I needed to eliminate the first letter of LastName from the vowel hunt. Yes, I missed that. BTW, nice way of eliminating the need of upper and lower within Filter()! And I wondered whether Y should be included but I figured the business can always tweak it at that point. :wink2:

[jrRaid]

Thank you all.

Not only a specific solution but also a more general way to solve similar problems.

Great.