 # from Function results to "real" numbers to calculate

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)

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

# Re: from Function results to "real" numbers to calculate

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 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.

# Re: from Function results to "real" numbers to calculate

@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

# Re: from Function results to "real" numbers to calculate

Hugo, you are welcome