from Function results to "real" numbers to calculate

%3CLINGO-SUB%20id%3D%22lingo-sub-1509977%22%20slang%3D%22en-US%22%3Efrom%20Function%20results%20to%20%22real%22%20numbers%20to%20calculate%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1509977%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20made%20a%20some%20nice%20excel%20sheets%2C%20but%20the%20problem%20is%20I%20can't%20calculate%20with%20the%20result%20of%20these%20functions%3CBR%20%2F%3EStart%20value%3A%20'%E2%80%AD(%E2%80%AD%E2%88%92%E2%80%AD158%E2%80%AC%E2%80%AC%7C%E2%80%AD39%E2%80%AC)%E2%80%AC%3CBR%20%2F%3E(imported%20from%20internet)%3CBR%20%2F%3EFirst%20function%3A%20%3DLINKS(%24E4%2CVIND.ALLES(%22%7C%22%2C%24E4)-1)%3C%2FP%3E%3CP%3EResult%20First%20function%3A%20'%E2%80%AD(%E2%80%AD%E2%88%92%E2%80%AD158%E2%80%AC%E2%80%AC%3CBR%20%2F%3ESecond%20function%3A%20%3DRECHTS(DEEL(M4%2C3%2C10)%2C10)%3CBR%20%2F%3EResult%20second%20function%3A%20%E2%88%92%E2%80%AD158%E2%80%AC%E2%80%AC%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20number%20I%20use%20in%20a%20distance%20calculation%3CBR%20%2F%3E%3DALS(EN(%24G8%3D0%2C%24F8%3D0)%2C%20%22%22%2C%20ALS(WORTEL((%24F8-M%245)%5E2%2B(%24G8-P%245)%5E2)%26lt%3B20%2CWORTEL((%24F8-M%245)%5E2%2B(%24G8-P%245)%5E2)%2F3%2F24%2FO%245%2C(((WORTEL((%24F8-M%245)%5E2%2B(%24G8-P%245)%5E2)-20)%2F3)%2F(1%2BN%243*0.2)%2B(20%2F3))%2F24%2FO%245))%3C%2FP%3E%3CP%3EI%20think%20because%20%3DValue()%20results%20in%20False%20that%20the%20results%20from%20the%20second%20function%20isn't%20a%20%22real%22%20number.%20is%20there%20a%20function%20to%20get%20this%20straight%3F%20I%20know%20about%20'copy'%20-%26gt%3B%20'paste%20value'%20but%20that%20isn't%20the%20solution%20I'm%20looking%20for.%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20can%20find%20them%20in%20Sheet%20'Targets'%20(first%20two%20functions)%20and%20'Calculations'%20(the%20last%20funtion).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHugo%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1509977%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1510124%22%20slang%3D%22en-US%22%3ERe%3A%20from%20Function%20results%20to%20%22real%22%20numbers%20to%20calculate%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1510124%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722724%22%20target%3D%22_blank%22%3E%40hugovanleeuwen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20since%20within%20your%20texts%20are%20non-printable%20characters%20with%20unicodes%208236%20and%208237%2C%20that's%20usual%20story%20with%20copy%2Fpasting%20data%20from%20web.%20You%20may%20check%20substituting%20above%20characters%20on%20something%20visible%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20640px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F204220i0737651B193AB8AE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EYou%20may%20try%20to%20play%20with%20Paste%20special%3B%20or%20replace%20UNICHAR(8236)%20and%20UNICHAR(827)%20(add%20such%20formulas%20to%20empty%20cells%2C%20copy%20value%2C%20Ctrl%2BH%20and%20replace%20it%20everywhere%20on%20nothing)%3B%20or%20play%20with%20your%20formulas%20to%20extract%20numbers%20without%20non-printable%20characters%20if%20they%20are%20always%20on%20same%20positions.%20Something%20like%20this.%20Not%20sure%20I%20found%20all%20non-printable%20characters%2C%20perhaps%20more%20of%20them%20are%20here.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOr%20use%20Power%20Query%20to%20pick-up%20data%20from%20web%20and%20transform.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1511121%22%20slang%3D%22en-US%22%3ERe%3A%20from%20Function%20results%20to%20%22real%22%20numbers%20to%20calculate%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1511121%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3EI%20have%20found%20the%20solution.%20because%20every%20combination%20has%20an%20unique%20lenght%20I%20combined%20the%20functions%20have%20serveral%20times%20with%20%3DIF%20and%20%3DLENGHT%20now%20it's%20working%20without%20problems.%20thanks%20for%20your%20suggestion%2C%20it%20was%20helpfull%20to%20keep%20looking%20and%20trying%20different%20things.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHugo%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1513278%22%20slang%3D%22en-US%22%3ERe%3A%20from%20Function%20results%20to%20%22real%22%20numbers%20to%20calculate%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1513278%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722724%22%20target%3D%22_blank%22%3E%40hugovanleeuwen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHugo%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have made a some nice excel sheets, but the problem is I can't calculate with the result of these functions
Start value: '‭(‭−‭158‬‬|‭39‬)‬
(imported from internet)
First function: =LINKS($E4,VIND.ALLES("|",$E4)-1)

Result First function: '‭(‭−‭158‬‬
Second function: =RECHTS(DEEL(M4,3,10),10)
Result second function: −‭158‬‬

This number I use in a distance calculation
=ALS(EN($G8=0,$F8=0), "", ALS(WORTEL(($F8-M$5)^2+($G8-P$5)^2)<20,WORTEL(($F8-M$5)^2+($G8-P$5)^2)/3/24/O$5,(((WORTEL(($F8-M$5)^2+($G8-P$5)^2)-20)/3)/(1+N$3*0.2)+(20/3))/24/O$5))

I think because =Value() results in False that the results from the second function isn't a "real" number. is there a function to get this straight? I know about 'copy' -> 'paste value' but that isn't the solution I'm looking for.

You can find them in Sheet 'Targets' (first two functions) and 'Calculations' (the last funtion).

 

Thank you,

 

Hugo

3 Replies

@hugovanleeuwen 

That's since within your texts are non-printable characters with unicodes 8236 and 8237, that's usual story with copy/pasting data from web. You may check substituting above characters on something visible like

image.png

You may try to play with Paste special; or replace UNICHAR(8236) and UNICHAR(827) (add such formulas to empty cells, copy value, Ctrl+H and replace it everywhere on nothing); or play with your formulas to extract numbers without non-printable characters if they are always on same positions. Something like this. Not sure I found all non-printable characters, perhaps more of them are here.

 

Or use Power Query to pick-up data from web and transform.

@Sergei Baklan 
I have found the solution. because every combination has an unique lenght I combined the functions have serveral times with =IF and =LENGHT now it's working without problems. thanks for your suggestion, it was helpfull to keep looking and trying different things.

 

Thanks,

 

Hugo