Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .