Numerology Gematria withbtwo words or more

I want to use a formula to translate text to numbers (Gematria).
I'm using this for the hebrew alphabet:
=SUM( 1* CHOOSE( CODE( MID( A5, ROW( INDIRECT("1:" & LEN(A5))), 1))-223, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 20, 30, 40, 40, 50, 50, 60, 70, 80, 80, 90, 90, 100, 200, 300, 400))
Some times there are two words seperated by a space in one cell and excel gives #VALUE.
How do i make that space wont give a error?

You can remove a space in a text by using SUBSTITUTE()

PHP Code: 

=SUM(1* CHOOSE(CODE(MID(SUBSTITUTE(A5," ",""), ROW(INDIRECT("1:" & LEN(SUBSTITUTE(A5," ","")))), 1))-223, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 20, 30, 40, 40, 50, 50, 60, 70, 80, 80, 90, 90, 100, 200, 300, 400)) 


Thank you so much. Works bautyfull.

Seems we are doing something very similar. I read your thread on how to get the blanks in between words not to show up on as #N/A. Still working on it but it helps. My main issue is working with the 5 Final Sofit letters. It seems Excel cannot differentiate beteen מ and ם. I have been using VLookup for much of my Gematria formulats but when trying to look up Final Sofits it pullhe value of 40 for Final Mem instead of 600 or sometimes its reversed and get 600 for Mem. I don't know if its how Excel reads the Hebrew Alphabet or not. Do you have any solutions. Thanks

Hi Aydy,
Could you share an Excel file with the code in question? I'm working on a gematria spreadsheet for Hebrew words but my knowledge of Excel is limited.

JCA