Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm using this formula to get value form a table


where C11 = 12354567

But when I use


where C11 = 12345678, the formula does not work.

Evaluating formula I have found out that LEFT adds double quotes making the formula like this:


How to approach that problem ?

share|improve this question
up vote 3 down vote accepted

You need to convert the text back to a number. You can do that by multiplying by 1 or adding 0 (or subtracting 0, or dividing by 1, well any arithmetic operation that doesn't change the final value):



Text operations (like LEFT, MID, RIGHT, etc) return text values and not numeric ones.

A more 'formal' way would be to use VALUE:

share|improve this answer

Left is returning a string, which I assume is being compared to numbers. This will fail. You need to change it to a number with something like Int(Left(C11)) or Value(Left(C11)).




share|improve this answer
+1 for value, but I would avoid int, since in that case, it works, but should the text be different (e.g. int(left(2.345,3)) would return 2 instead of 2.3. – Jerry Apr 5 '14 at 13:27
@Jerry I agree, I should have mentioned only use int with integers. Sometimes I forget the simple things. – Raystafarian Apr 5 '14 at 13:29

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.