h2g2 The Hitchhiker's Guide to the Galaxy: Earth Edition

I'm useless at Excel and useless at using the Excel help pages.

Therefore if someone can help me
I have a column with characters (mixed numbers and letters). They are IBAN numbers and I want to quickly check if they have been correctly entered. They are either 8 or 11 so one way to check if they are correct is to display the number of characters in the cell.

Is there a way of doing that? I want to add a column next to this one which displays in the adjacent cell to the IBAN number how many characters are in that cell

If it makes sense.

Try =len(A1) if, A1 is the cell you want to check.


Geggs

brilliant - thanks!!

I find Excel help fairly useless. I google the thing I'm trying to figure out and usually find the answer that way. I taught myself pivot tables that way.

Yep, it's possible! You just need to add an if statement to check the number sizes, then you can format the next column to give you your output

thanks for coming fords, but... I don't understand a word of that!

the =len() worked perfectly

Oops, I should take my programmer's hat off next time! Glad you got it sorted in the end

The Mr Plod way that I would first have thought of would be to take the differences between A1-A2, then A2-A3 etc then just run my eye down the resulting column to see if any of the numbers have an inordinately large number of digits. Rough and ready.

I like Excel, but I don't need it for much more than basic arithmetic, averages, etc.

what I needed to check was how many characters were in each cell - they weren't numbers - just combinations of letters/numbers. They had to be either 8 or 11 characters long

the solution from Geggs (and probably the other one - was it Fords? - if I could have understood it was perfect

Rather than check them manually, could you also use Data Validation?

Highlight the column and select Data Validation. It's on the 'Data' menu in Office 2010 (assume it's the same for previous). Change the 'Allow' drop-down to 'Custom', and enter the formula:

=OR(LEN(B1)=8,LEN(B1)=11)

Thankfully, Excel has the common sense to apply this to the whole column. Then it will actively prohibit non-conforming data from being entered, rather than you having to sift it manually.

B

i might have a go at that tomorrow - it's not actually my list so I'll have to work out how best to use it

Ah yes. I forgot that IBAN numbers are alphanumerical. *crawls back into shell*

and thank you!!! I'Ve been trying to remember alphanumerical since I wrote the first post.

I'm having terrible terrible problems these days - when I start a sentence I know what all the words are that I need, but sometimes I get half way through and a word has just vanished and I have no idea what it should be. And that happens in English as much as German now.

hi Mr B

so - I tried your suggestion - by copy/paste what you wrote. I think what

=OR(LEN(B1)=8,LEN(B1)=11)

means is that whatever goes in the cell B1 can be only either 8 or 11 characters long?

because I'm getting an error message, and I really don't understand anything about Excel at all.

also, sorry, that means you have to enter the formula before you start to enter data, right? you can't make a new column, set those parameters then copy/paste data from another column into it?

(how do you all have all this excel expertise, by the way?)

You can add the formula at any time. As long as it points to the right cell, it will work.

I tried to apply it to a colum that already exists without much success
I'll try it again

nope, didn't work. I'm very confused by this, actually.

Sometimes formula formats change from version to version of Excel.