Jul 08 2020 12:40 PM
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
Jul 08 2020 01:34 PM
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.
Jul 09 2020 01:47 AM
@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
Jul 09 2020 04:08 PM
Hugo, you are welcome