CODEWITHSUNDEEP
excelpowerpivotdax
Miller86
Miller86

Reputation: 404

Why won't this DAX formula work?

I'm currently using a DAX formula to try and remove numbers from a string. The string is the first half of a postcode. I've therefore typed the following formulae

=if(Istext(mid([Postcode District],1,1)),(left([postcode district],2)),(left([postcode district],1)))

what this SHOULD do is see if the second character is text and return 2 letters if it is, one if it isn't.

For example, input CA1 should return CA whilst B22 should return B

This simply isn't doing that, and I'm not sure why. all that is being returned is the first two letters whether the second letter is text OR numeric.

Upvotes: 1

Views: 245

Answers (2)

Gordon K
Gordon K

Reputation: 824

The MID function takes a string as an argument and will always return a string (even if it looks like a number), so your IsText is always TRUE.

Try this

=if(IsNumber(mid([Postcode District],1,1)*1),(left([postcode district],1)),(left([postcode district],2)))

Upvotes: 1

Miller86
Miller86

Reputation: 404

ended up using =SUBSTITUTE(substitute(SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE([Postcode District],"1"," "),"2"," "), "3"," "),"4"," "),"5"," "), "6"," "),"7"," "),"8"," "),"9"," "),0,"") (as its DAX it'll accept this - it's too many nested functions for standard XL)

As it's quite an ugly formulae, I'll be trying other people's answers too, to see if there's a more elegant solution :)

Upvotes: 0

Related Questions