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