 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
Got a long list of distances. All in cells A1 to A10000.
Then cells next to it (B1 to B10000) say either m (metres) or Km. Can I chuff make it so that C1 works (if B1="m", A1, A1*1000).
Help!
I can email the spreadsheet if that helps.
It's for my 2015 Transalp Adventure.
C
This works for me
=IF(B1="m",A1,A1*1000) Bracket in the wrong place for you.
no, still not working! Have emailed it to you, hope you dont mind?
C
your m in your sheet is actually "m "
Tried that and it didnt seem to do anything. Have you tried it?
c
I deleted the space after the m and it worked. excel seems to not like doing a comparison on "m "
If you want an auto correct something like =IF(LEN(B1)=2,LEFT(1),LEFT(2)) should return you the cropped characters.
or use =trim(B1)
ggrrrr still cant make anything work to simply return the metres or *1000 if it's Km
ggrrrrr
C
but have noticed tha the =trim command doesn't work on it either. what the heck???
C
Some of it works what I try it in your sheet, others don't. For the time it's taken and the number of KM entries I'd do a manual job on it.
oh.... errors here we come!
surely there should be a simple way?
Check the format of the cells. Sounds like there is variability which is stopping functions work some of the time.
Can you filter on the m / km ok?
No. I formatted them all as text, then tried it as 'geneal, then as 'numbers' but still didnt work. Have just done it al manually but a pain and I need to know why as I'm like that..
C
Copy paste special the values into a new workbook and apply the suggested formulas to the data in the new workbook
apply a transformation to all the data such as =A1*100/100 which should force numerical format.
sounds like a lot of data so sometimes it won't auto calculate. I think it is shift and f9 to force it is(once everything else is correct of course)
ggrrrr still cant make anything work
When you type the formula in C1 & hit enter what do you actually see in C1?
Chris,
I looked at the actual content of the file you sent me through...
The cells DON'T contain "m<space>" they contain "m<unicode char 0A>" which is a 'non breaking space'.
Hence the IF= is not working
Similarly TRIM which removes spaces.
This does work:
=IF(LEFT(E4,1)="m",TRUE,FALSE)
So does this:
=IF(E4=$K$2,TRUE,FALSE)
if you simply copy & paste an "m<nbsp>" cell to K2
Or you can enter a non breaking space (Mac is ALT+Space, IIRC Windows is CTRL+SHFT+Space)
Or you can create it by puting this in cell K2:
="m"&CHAR(202)
Hope that explains!
Poly,
thank you for your help. That makes sense. I was getting really annoyed! I had pasted in all the data from Memory Map from which we have all the Swiss and French maps and I have mde up the route that's about 380Km so a fair amount of workdone (so far!)
chers
Cx