Cannot convert text to numbers in a calculated column

[JohnSonn]

Hi, this is my first post here.

I am trying to create a calculated column that extracts the first two letters of a postcode or first letter of a postcode if the second digit is a number.

Power query however stores the entire thing as text and although value works in excel it does not work in power query.

This is the formula I have tried:

=LEFT(December_Entry[Postcode],1)&IF(ISNUMBER(VALUE(RIGHT(LEFT(December_Entry[Postcode],2),1),"",RIGHT(LEFT(December_Entry[Postcode],2),1))

However this always gives me error because VALUE(RIGHT(LEFT(December_Entry[Postcode],2),1) returns an error since the column contains part text and numbers. Even if I create a new column using RIGHT(LEFT(December_Entry[Postcode],2),1) and then take the VALUE of this columns I still get an error.

In my desperation I tried using this:

=IF(RIGHT(LEFT(December_Entry[Postcode],2),1)=OR("9",OR(OR(OR("3","4"),OR("1","2")),OR(OR("5","6"),OR("7","8")))),"",RIGHT(LEFT(December_Entry[Postcode],2),1))

But this does not work either. Can anyone help?

Many thanks in advance

[az38]

Hi @JohnSonn 

try a column

Column = 
var _secondSymbol = MID(December_Entry[Postcode];2;1)
return
if (NOT(ISERROR(VALUE(_secondSymbol)));BLANK();_secondSymbol)

or (worse)

Column = 
var _secondSymbol = MID(December_Entry[Postcode];2;1)
return
if (_secondSymbol IN {"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"};BLANK();_secondSymbol)

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[JohnSonn]

Hi there az38,

I have tried implementing the code that you gave me into the 'Custom Column' function but I am getting various errors.

Can you please tell me where I should enter these lines of code?

[JohnSonn · Accepted solution]

I have fixed the error, this is the calculated column I should be using:

=LEFT(December_Entry[Postcode],1)&IF(RIGHT(LEFT(December_Entry[Postcode],2),1)="0" || RIGHT(LEFT(December_Entry[Postcode],2),1)="1" || RIGHT(LEFT(December_Entry[Postcode],2),1)="2" || RIGHT(LEFT(December_Entry[Postcode],2),1)="3" || RIGHT(LEFT(December_Entry[Postcode],2),1)="4" || RIGHT(LEFT(December_Entry[Postcode],2),1)="5" || RIGHT(LEFT(December_Entry[Postcode],2),1)="6"|| RIGHT(LEFT(December_Entry[Postcode],2),1)="7" || RIGHT(LEFT(December_Entry[Postcode],2),1)="8" || RIGHT(LEFT(December_Entry[Postcode],2),1)="9", "", RIGHT(LEFT(December_Entry[Postcode],2),1))