Having issues with IF statements referencing Concatenated cells

[petethickett]

So I am putting together a file which starts off with a part number, similar to that of a VIN.

Initially I had a series of drop down lists that ensured that only correct values are picked in each cell. This worked fine with plenty of IF statements referencing each segment of the part number to put together a bill of materials.

Now I had to change the file so that people can paste a part number into a cell. So my solution was to turn my cells that used to be set up with the lists, into a series of CONCATENATE formula, pulling from the cell the user would paste the part number into. So in theory the rest of my spreadsheet works as before.

However for some reason any time an IF statement in a cell points to one that contains the CONCATENATE formula, I get an error, but ONLY when it is a number? The cells that (after the CONCATENATE) result in letters, I have no issues. Its really infuriating me!

Any help would be fantastic!

[Andrew-R]

When you concatenate two numbers together Excel will automatically treat them as a string, not a number, so you have to be careful.

For example, if A1 and B1 both contain the number 1 then:

=A1 & B1

Will return the string "11", not the number 11.

[On a side note, don't use CONCATENATE, it's a waste of typing time, just use & between any values you want to join]

So, if the result of the concatenation was in cell C1 then an IF statement which starts:

=IF(C1=11,

Will return a false value. Either chance it to:

=IF(C1="11"

Or amend the formula where you're joining the cells to:

=VALUE(A1 & B1)

[petethickett]

Wow thanks for the fast response!

I just used the =VALUE() option and it worked like a champ.

I have heard that I should be using & instead, and normally I do when I am just joining cells together. But can I use it to in place of a formula like =CONCATENATE(MID(B3,4,2)) and things like that?

Thanks again, you just saved my sanity!

[Andrew-R]

Happy to help and, yes, you can use & anywhere where you'd normally use CONCATENATE. So, say:

=CONCATENATE(MID(B3,4,2)," ",RIGHT(B3,1))

Would become:

=MID(B3,4,2) & " " & RIGHT(B3,1)

[petethickett]

Thats great, thankyou very much!