SORRY - A function needed to derive *** from a social security number

[Slav3]

Sorry in andvance for flooding your forum with a thread that's propably way out of place.

I must also note that I am a total newbie in terms of using Excel, yet I have been given the duty of helping a wide variety of people with it's use.

Now to the subject itself, the Finnish social security number consists of 11 digits, one being the dash separating the first 6 and last 4 digits.

123456-9876 for example

The first six digits define age (ddmmyy), and the first three of the last four digits define GENDER FOR CHRISTSSAKE (even being female and odds being male).

All of this information is going to be placed in a single cell, and I would need a function that can pick those first three digits from the last four number cluster, divide that number and see whether it's a he or a she.

Can someone please help me out?

[Andrew-R]

With the SSN in cell A1 then something like this, maybe:

=IF(ISEVEN(VALUE(MID(A1,FIND("-",A1)+1,3))),"Female","Male")

[martindwilson]

try
=IF(ISEVEN(--MID(A1,FIND("-",A1)+1,3)),"female","male")
well that's the same then lol

[Kevin UK]

Hi Slav3

Try the following assuming the Security number is in A1

Please Login or Register  to view this content.

Edit: Swap the male & female around, just looked at the post again!

[martindwilson]

3's a charm

[Andrew-R]

Well, just for a change, how about pointlessly complicated

=PROPER(LEFT("fe",2*(1-MOD(INT(SUBSTITUTE(A1,"-","")/10),2))) & "male")

[Slav3]

Well as I said I am as completely useless with Excel as those that I am helping.

For example, when I paste the function into the function-field, it gives me a pop-up saying there's an error in the function and tries to direct me to Ms's own help.
It's hard to even describe how frustrating this is, I can't even modify the function so that it would apply to all the cells in column A.

Thanks a bunch to ya'll, making my day look like I'm actually doing something worthwhile.

[martindwilson]

mind you since they are all the same length

=IF(ISEVEN(MID(A1,10,1)),"female","male")

[Andrew-R]

The local settings on your Excel might use semi-colons, rather than commas, as separators in functions, so try:

=IF(ISEVEN(VALUE(MID(A1;FIND("-";A1)+1;3)));"Female","Male")

All of these functions also rely on the SSN being in cell A1. If it's in a different cell then you'll have to change all of the A1 references to that cell address.

[Andrew-R]

I've just noticed the title of this thread.

Have we got the world's most prudish censor-bot, or what? You can have a gender, but you can't have ***.

Where does it think little censor-bots come from?

[Slav3]

I think I may have managed to narrow down the error in my case, after closing the pop-up, a part of the function becomes highlighted, in this case "Female","Male". Doesn't matter whether I use the function you gave last or the one in post # 3

[Andrew-R]

I think we're nearly there - I just forgot to replace the last comma with a semi-colon:

=IF(ISEVEN(VALUE(MID(A1;FIND("-";A1)+1;3)));"Female";"Male")

Or you could use Martin's formula, which is a lot shorter:

=IF(ISEVEN(MID(A1;10;1));"female";"male")

[Slav3]

=IF(ISEVEN(VALUE(MID(A1;8;3)));"female";"male")

Hi, I just thought to let ya'll know that I found a solution thanks to your help. I don't find it likely I'd have found out the MID function by myself.

A very handy site, I'll be returning for more advice in the future.

[vlady]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as "SOLVED".
For the meantime I'll do it for you.

How?
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Note:
You can also thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given.
By doing so you can add to the reputation(s) of those who helped and shared their time in helping you.