How to split postal area from post code

masplin

Active Member
Joined
May 10, 2010
Messages
406
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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,824
Hi Mike

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

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

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

masplin

Active Member
Joined
May 10, 2010
Messages
406
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

Active Member
Joined
May 10, 2010
Messages
406
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.

Code:
=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

Board Regular
Joined
Jul 28, 2011
Messages
190

ADVERTISEMENT

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

Active Member
Joined
May 10, 2010
Messages
406
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

Active Member
Joined
May 10, 2010
Messages
406

ADVERTISEMENT

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:

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

R Pelle

Board Regular
Joined
Jul 28, 2011
Messages
190
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

Board Regular
Joined
Aug 31, 2008
Messages
171
masplin,
Try this:
Code:
=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

Active Member
Joined
May 10, 2010
Messages
406
Yes bit more elegant than my version, but I'm a bit unlcear why it works!

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,740
Messages
5,524,557
Members
409,584
Latest member
Devil_717

This Week's Hot Topics

Top