How to split postal area from post code

[masplin]

I have UK post codes in the following formats
A1
AA1
A11
AA11

I need to extract just the letters in order to look up the city. In excel I just do this with checking if the 2nd digit is a number using ISNUMBER....simple. I am a newbie to powerpivot, but have tried every trick to get the same function to work without sucess.

The problem is when you use MID on this data it doesn't recognise the 2nd digit as a number and thinks it is text. I tried to use VALUE on the 2nd character, but then throws an error if the character is actually text. I tried to use an IF statement, but doesn't like one value being TEXT and the other being a NUMBER!!!!

Must be an easy solution!!!!

Thanks

Mike

 

[pgc01]

Hi Mike

=ISNUMBER(MID(A1,2,1))

doesn't work because Mid() returns a text value. Use

=ISNUMBER(-MID(A1,2,1))

 

[masplin]

Doesnt work as says "cannot convert value "a" of type string to value real" where "A2 in the 2nd character. i'm assuming it is just a minus sign in front of MID?

 

[masplin]

for what its worth I had to do a long winded error check if value of the 2nd character within an iff statement. Works, but horrible.

=upper(if(iserror(value(mid(users[Primary Post Code],2,1))),left(users[Primary Post Code],2),left(users[Primary Post Code],1)))

 

[R Pelle]

Just tried PGC01's formula in column B Assuming you postal codes are in Col A.
It should return the following values
col A, col B
A1, True
AA1, False
A11, True
AA11, False
and yes it is a minus sign.

In column C I added the following formula
=IF(ISNUMBER(-MID(A1,2,1))=TRUE,LEFT(A1,1),LEFT(A1,2))
and it returns
A
AA
A
AA

(Using Mac Excel 2011)

 

[masplin]

I have just upgraded to the 2012 beta, but get exactly the same error taking exactly your formula and pasting in. I just changed the A1 to [Primary Post Code] which holds the post code. My post code is actually AA11 1AA but the error seems to be indicating the 2nd character is the problem.

Out of interest what is "-" sign doing in the formula?

I'll try a blank sheet.

 

[masplin]

I think we talking a cross purposes as I wasn't 100% clear in my orignal post that I'm working within powerpivot. This in NOT an excel formula for example in column B. Excel is easy. This is a DAX formula within the powerpivot DB. I have linked my table with the full post code so it appears in powerpivot as [Post code]. I wanted to look up the correct city within powerpivot using another lined tables that maps the letters to the city. So my thoguht was to just put a DAX formula in the next column to do this. So something like:

=IF(ISNUMBER(-MID([Primary Post Code],2,1))=TRUE,LEFT([Primary Post Code],1),LEFT([Primary Post Code],2))

 

[R Pelle]

Ah, ok that clears up the confusion, I think I'll leave this one for PGC to pick up further. I assumed wrong and thought the thread was in the wrong place. It happens...

 

[ruve1k]

masplin,
Try this:

=LEFT([Primary Post Code],1+ISERROR(-MID([Primary Post Code],2,1)))

Note that you don't need the UPPER function if your purpose is to use it to look up the city since relationships are not case sensitive.

 

[masplin]

Yes bit more elegant than my version, but I'm a bit unlcear why it works!

ISERROR(-MID([Primary Post Code],2,1)

This checks if the 2nd character is an ERROR, but want does "-MID" mean?

In order to only give me the 1st character when the string is A1 the code above must be returning FALSE = 0. So letters make the -MID throws an error and digits don't.