It would be great to calculate the number from a Name.

I came across this calculation from ISO Productions:

Left(

Int(Position("qz---abcdefghijklmnoprstuvwxy", Middle(Name, "1", 1), "1", 1) / 3) &

Int(Position("qz---abcdefghijklmnoprstuvwxy", Middle(Name, "2", 1), "1", 1) / 3) &

Int(Position("qz---abcdefghijklmnoprstuvwxy", Middle(Name, "3", 1), "1", 1) / 3) &

Int(Position("qz---abcdefghijklmnoprstuvwxy", Middle(Name, "4", 1), "1", 1) / 3) &

Int(Position("qz---abcdefghijklmnoprstuvwxy", Middle(Name, "5", 1), "1", 1) / 3) &

Int(Position("qz---abcdefghijklmnoprstuvwxy", Middle(Name, "6", 1), "1", 1) / 3) &

Int(Position("qz---abcdefghijklmnoprstuvwxy", Middle(Name, "7", 1), "1", 1) / 3) &

Int(Position("qz---abcdefghijklmnoprstuvwxy", Middle(Name, "8", 1), "1", 1) / 3) &

Int(Position("qz---abcdefghijklmnoprstuvwxy", Middle(Name, "9", 1), "1", 1) / 3) &

Int(Position("qz---abcdefghijklmnoprstuvwxy", Middle(Name, "10", 1), "1", 1) / 3),

Length(Name)

)

[color:"purple"]

The problem with this calculation is that it creates the Q and Z into a 0. Which should be a 7 for Q and 9 for Z. Also I need to be able to put " " (space) & 0123456789 between the name without having it set to a 0.

I'm not sure what would be the best approach.

Help is much appreciated.

Post deleted by Ugo DI LUCA

Hi,

Strange but interresting approach of phone numbers...

Left(

Case(Middle(Name, 1, 1) ="Q", 7, Middle(Name, 1, 1) = "Z",9,Middle(Name, 1, 1) = " "," ",

Int(Position("qz---abcdefghijklmnoprstuvwxy", Middle(Name, 1, 1), "1", 1) / 3)) &

Case(Middle(Name, 2, 1) ="Q", 7, Middle(Name, 2, 1) = "Z",9,Middle(Name, 2, 1) = " "," ",

Int(Position("qz---abcdefghijklmnoprstuvwxy", Middle(Name, 2, 1), "1", 1) / 3))&

Case(Middle(Name, 3, 1) ="Q", 7, Middle(Name, 3, 1) = "Z",9,Middle(Name, 3, 1) = " "," ",

Int(Position("qz---abcdefghijklmnoprstuvwxy", Middle(Name, 3, 1), "1", 1) / 3)) &

Case(Middle(Name, 4, 1) ="Q", 7, Middle(Name, 4, 1) = "Z",9,Middle(Name, 4, 1) = " "," ",

Int(Position("qz---abcdefghijklmnoprstuvwxy", Middle(Name, 4, 1), "1", 1) / 3)) &

Case(Middle(Name, 5, 1) ="Q", 7, Middle(Name, 5, 1) = "Z",9,Middle(Name, 5, 1) = " "," ",

Int(Position("qz---abcdefghijklmnoprstuvwxy", Middle(Name, 5, 1), "1", 1) / 3)) &

Case(Middle(Name, 6, 1) ="Q", 7, Middle(Name, 6, 1) = "Z",9,Middle(Name, 6, 1) = " "," ",

Int(Position("qz---abcdefghijklmnoprstuvwxy", Middle(Name, 6, 1), "1", 1) / 3)) &

Case(Middle(Name, 7, 1) ="Q", 7, Middle(Name, 7, 1) = "Z",9,Middle(Name, 7, 1) = " "," ",

Int(Position("qz---abcdefghijklmnoprstuvwxy", Middle(Name, 7, 1), "1", 1) / 3)) &

Case(Middle(Name, 8, 1) ="Q", 7, Middle(Name, 8, 1) = "Z",9,Middle(Name, 8, 1) = " "," ",

Int(Position("qz---abcdefghijklmnoprstuvwxy", Middle(Name, 8, 1), "1", 1) / 3)) &

Case(Middle(Name, 9, 1) ="Q", 7, Middle(Name, 9, 1) = "Z",9,Middle(Name, 9, 1) = " "," ",

Int(Position("qz---abcdefghijklmnoprstuvwxy", Middle(Name, 9, 1), "1", 1) / 3)) &

Case(Middle(Name, 10, 1) ="Q", 7, Middle(Name, 10, 1) = "Z",9,Middle(Name, 10, 1) = " "," ",

Int(Position("qz---abcdefghijklmnoprstuvwxy", Middle(Name, 10, 1), "1", 1) / 3)),

Lenght(Name)

)

Thanks Ugo,

I was hoping that it could be done without the use of the Case because it would make the calculation very long considering that I have it until (Name, 20, 1) and am using the q,z," ", and 1 to 9. But it works fine like this.

Thanks!

You could try:

Left(

Int(Position("-------abc-def-ghi-jkl-mno-pqrstuv-wxyz", Middle(Name, 1, 1), 1, 1) / 4) &

Int(Position("-------abc-def-ghi-jkl-mno-pqrstuv-wxyz", Middle(Name, 2, 1), 1, 1) / 4) &

Int(Position("-------abc-def-ghi-jkl-mno-pqrstuv-wxyz", Middle(Name, 3, 1), 1, 1) / 4) &

Int(Position("-------abc-def-ghi-jkl-mno-pqrstuv-wxyz", Middle(Name, 4, 1), 1, 1) / 4) &

Int(Position("-------abc-def-ghi-jkl-mno-pqrstuv-wxyz", Middle(Name, 5, 1), 1, 1) / 4) &

Int(Position("-------abc-def-ghi-jkl-mno-pqrstuv-wxyz", Middle(Name, 6, 1), 1, 1) / 4) &

Int(Position("-------abc-def-ghi-jkl-mno-pqrstuv-wxyz", Middle(Name, 7, 1), 1, 1) / 4) &

Int(Position("-------abc-def-ghi-jkl-mno-pqrstuv-wxyz", Middle(Name, 8, 1), 1, 1) / 4) &

Int(Position("-------abc-def-ghi-jkl-mno-pqrstuv-wxyz", Middle(Name, 9, 1), 1, 1) / 4) &

Int(Position("-------abc-def-ghi-jkl-mno-pqrstuv-wxyz", Middle(Name, 10, 1), 1, 1) / 4) ,

Length(Name)

)

<<edited: I had the number off by one>>

Oh Bob it works like a charm...

I would have bet to have tested the calc you posted too. I must have screwed up somewhere cause it wasn't working as such.....

Well, he just needs a case for the space now...

I was coming back here for another workaround. Even if not neccessary now, you can avoid 2 of 3 case statements in my first post using a nested

Substitute(Substitute(Name,"Z", "Y"); "Q", "R") instead of "Name".

That's just great Bob!!!

Indeed I'm using a Case for the space but also for the 1 to 9 otherwise they become a zero.

Thanks for your time Ugo.