SpreadEveryone: The Fetchland Excel wire

98 watchers

GlennR May 2022 7:40pm, 6 May 2022 63,467 posts	0 GlennR Sk, it’s an odd one. I can’t reproduce the error, =C5<D5 applies the format to C5 as you’d expect. Excel for Mac, latest version.
sallykate May 2022 8:27pm, 6 May 2022 9,785 posts	0 sallykate Suggests I'm doing something odd...I may try again with some dummy data for my sanity's sake. The figures I have are copied from an email, which was copied from a PPT file. (Working on a PC, MS 365 Apps for Enterprise.) So I may also make a clean version to see if that behaves. Thank you for the suggestion Bowman. Unfortunately I can't use a standard setting; I want to format lots of individual cells to try to get patterns to stand out.
um May 2022 8:34pm, 6 May 2022 6,041 posts	0 um This work for me sallykate
JCB May 2022 2:14pm, 7 May 2022 3,229 posts	0 JCB Maybe one cell is being treated as numeric and the other text? Or something similar
run free May 2022 9:57pm, 7 May 2022 4,969 posts	0 run free Hey sally write the formula into your worksheet and see which is returning True. Copy the formula down to make sure it is correct against the other numbers
sallykate May 2022 7:53pm, 8 May 2022 9,795 posts	0 sallykate Thank you everyone! I just tried it with "clean" numbers and it works. So there must be something weird with the numbers which have been pasted in - I just can't tell what it was. I had formatted all the cells as numbers, to try to make sure it wasn't a text v number thing. Can only think there's some weird invisible formatting/character which was making it all odd. But if I select and copy > paste values in the same place it still doesn't work. It only works if I re-type. Going to stop worrying about it now and resign myself to re-typing (thankfully not that many). (For anyone in a university: this is all related to REF for results week...good luck to anyone tasked with analysis...)
SPR May 2022 8:00pm, 8 May 2022 36,753 posts	0 SPR Formatting as number never works to convert numbers stored as text. You'll need to use text to columns or wrap your number with value.
GregP May 2022 8:05pm, 8 May 2022 135,104 posts	0 GregP Can’t you try multiplying it by one?[possible Ugo, sorry if so] #ugo
SPR May 2022 8:07pm, 8 May 2022 36,754 posts	0 SPR This has instructions on converting using text to columns. helpdeskgeek.com
Fizz :-) May 2022 9:51pm, 8 May 2022 7,015 posts	0 Fizz :-) If you do =LEN() on the text you can identify if there are hidden characters and then some form of =Mid can remove them.