excel vlookup equivalent?

[flyboy_yvr]

Hi

I want to move a database from Excel to FileMaker. I have most of the conversion sorted out, but one thing that I want to do is convert a field of up to 5 characters to morse code. I have this working well in Excel using the following formula:

=IF(MID(S2,1,1)="","",IF(MID(S2,2,1)="",VLOOKUP(MID(S2,1,1),Code!A:B,2),IF(MID(S2,3,1)="",VLOOKUP(MID(S2,1,1),Code!A:B,2)&" "&VLOOKUP(MID(S2,2,1),Code!A:B,2),IF(MID(S2,4,1)="",VLOOKUP(MID(S2,1,1),Code!A:B,2)&" "&VLOOKUP(MID(S2,2,1),Code!A:B,2)&" "&VLOOKUP(MID(S2,3,1),Code!A:B,2),IF(MID(S2,5,1)="",VLOOKUP(MID(S2,1,1),Code!A:B,2)&" "&VLOOKUP(MID(S2,2,1),Code!A:B,2)&" "&VLOOKUP(MID(S2,3,1),Code!A:B,2)&" "&VLOOKUP(MID(S2,4,1),Code!A:B,2),VLOOKUP(MID(S2,1,1),Code!A:B,2)&" "&VLOOKUP(MID(S2,2,1),Code!A:B,2)&" "&VLOOKUP(MID(S2,3,1),Code!A:B,2)&" "&VLOOKUP(MID(S2,4,1),Code!A:B,2)&" "&VLOOKUP(MID(S2,5,1),Code!A:B,2))))))

It's probably not the most compact code (!) but it works. It looks at each character in a cell (S2 in this case) and then does a lookup to another sheet (Code) for the equivalent value. It also makes sure that blanks are not added if there are less than 5 characters.

How do I go about doing this in FileMaker? I am not sure where to even start!

Many thanks in advance

J

[mr_vodka]

BTW you could achieve this without a custom function if you want with just a substitute function in a calculation.

Substitute ( Upper(EnteredText); ["A"; ".-"]; ["B"; "-..."]; ["C"; "-.-."]; ... ["Z"; "--.."]; [" "; ""]

etc

Edited February 17, 2007 by Guest
Removed Left Function

[comment]

Ahem... the Left() function seems a bit misplaced - assuming one wants the code for up to 5 characters, not 5 units of code.

[mr_vodka]

Well, that was a big ooops... :

Technically, since the field is supposed to have 5 characters. The Left function is not even needed as long as the validation is done on the source field.