from Function results to "real" numbers to calculate

[hugovanleeuwen]

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

[#2]

@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

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.

[#3]

@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

[#4]

@hugovanleeuwen 

Hugo, you are welcome