Logical Test

Hi,

I have a rows of data starting from A5:A500, the data in each cell has 10 characters (123456789A). In cell B5, i have used the following formula =LEFT(A5,4), as i'm only interested in the first 4 characters (these should be the same for all entries in A5:A500 (1234)). So far no problem, now in C5, I have entered my logical test =IF($F$1=B5,1,0), here is where the problem is, both B5 (1234) and F1 (1234) have the same data visually, I have ensured that both cells are formatted in number format, i even checked character lenght by using a formula, both have the same lenght.

Is the formula I have used in B5 not suitable in this case as when i manually type 1234 into B5 my logical test formula works

The LEFT function returns a string result, so you can't compare that directly with a number in F1. You can either precede the number in F1 with an apostrophe (i.e. enter it as '1234 - the apostrophe won't show in the cell), which will turn that into a text value, or you can format the cell F1 as Text, or you can amend your formula in B5 to this:

=--LEFT(A5,4)

which will turn that result into a number.

Hope this helps.

Pete

Thanks Guys the =--Left(A5,4) worked.

Originally Posted by Pete_UK

The LEFT function returns a string result, so you can't compare that directly with a number in F1. You can either precede the number in F1 with an apostrophe (i.e. enter it as '1234 - the apostrophe won't show in the cell), which will turn that into a text value, or you can format the cell F1 as Text, or you can amend your formula in B5 to this:

=--LEFT(A5,4)

which will turn that result into a number.

Hope this helps.

Pete

Hi Pete,

I now have a value which consists of both numerical and text characters, when I use formula =--LEFT(A5,4) it returns a #value! error, but when I remove the -- in the logical formula and insert ' before the look up value in F1 it works. Is it possible to modify the logical test formula for where alphanumerical values are used, as i don't want to have to modify the value in F1

Thanks,
Brian.

I think the easiest solution is to format cell F1 as Text, so that anything you put in there will be treated as a text value and you don't have to use a leading apostrophe, even for pure numbers. Then you can just use =LEFT(A5,4) in B5 to cope with all cases.

If you really want to keep F1 formatted as General, then you would need to do something like this in B5:

=IF(ISNUMBER($F$1*1),--LEFT(A5,4),LEFT(A5,4))

Hope this helps.

Pete

Thanks Pete reformatting of the cells worked great, thanks for your help

Glad to be of help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete