cancel
Showing results for
Did you mean:
Frequent Visitor

## IF statement with LEN, LEFT & RIGHT formulas

Hello, new to Power Bi, sorry if this is a basic question.

I have a column with 2 types of data (lets call it Flight No Colunn) : BA254/BA879 and BA463/548

I have created a helper column that returns the length of each cell (11 or 9)

I would like to create an if command like this:

IF(LEN[Flight No Column]=11, RIGHT[Flight No Column, 5], CONCATENATE(LEFT([Flight No Column], 2), RIGHT([Flight No Column], 5))

However, this does not work as I am combining numbers with text in the same formula.

Do you have any suggestions?

Thank you very much

1 ACCEPTED SOLUTION

Accepted Solutions
Resolver II

## Re: IF statement with LEN, LEFT & RIGHT formulas

i only changed the position of the parentheses

`Column 2 = IF(LEN(Table1[Flight No Column]) =11, RIGHT(Table1[Flight No Column], 5), CONCATENATE(LEFT(Table1[Flight No Column], 2), RIGHT(Table1[Flight No Column], 3))`
10 REPLIES 10
Resolver II

## Re: IF statement with LEN, LEFT & RIGHT formulas

Can you provide an example of the output you are looking for in both scenarios (LEN 9 and 11). I ran your code, adding the appropriate () and this is the result:

Frequent Visitor

## Re: IF statement with LEN, LEFT & RIGHT formulas

Thank you for your time. Trying to replicate your code i get this

Resolver II

## Re: IF statement with LEN, LEFT & RIGHT formulas

`if (LEN(Table1[Column]) = 11, right(Table1[Column],5), CONCATENATE(left(Table1[Column],2),RIGHT(Table1[Column],3)))`

is the correct function

Frequent Visitor

## Re: IF statement with LEN, LEFT & RIGHT formulas

With the above code, I can not go to the second step of the if command as shown here:

I am still failing to write down the correct syntax.

Resolver II

## Re: IF statement with LEN, LEFT & RIGHT formulas

Can you please share the yellow error message in the screenshot. Also try to copy/paste this :

`Column = if (LEN(Table1[Flight No Column]) = 11, right(Table1[Flight No Column],5), CONCATENATE(left(Table1[Flight No Column],2),RIGHT(Table1[Flight No Column],3)))`
Frequent Visitor

## Re: IF statement with LEN, LEFT & RIGHT formulas

@NipponSahoreunfortunately your code is not working

I can not replicate the working example from @BKirsch12

I am uploading my file, would you mind taking a look ?

The error I am getting is this one:

Resolver II

## Re: IF statement with LEN, LEFT & RIGHT formulas

The problem is the LEN function. This is what you had in the file you sent:

`Column 2 = IF(LEN(Table1[Flight No Column] =11), RIGHT(Table1[Flight No Column], 5), CONCATENATE(LEFT(Table1[Flight No Column], 2), RIGHT(Table1[Flight No Column], 3))) `

The length (LEN) function needs to return a value before it can be compared to another number. This is how it should be:

`Column 2 = IF(LEN(Table1[Flight No Column]) =11, RIGHT(Table1[Flight No Column], 5), CONCATENATE(LEFT(Table1[Flight No Column], 2), RIGHT(Table1[Flight No Column], 3)))`

Resolver II

## Re: IF statement with LEN, LEFT & RIGHT formulas

i only changed the position of the parentheses

`Column 2 = IF(LEN(Table1[Flight No Column]) =11, RIGHT(Table1[Flight No Column], 5), CONCATENATE(LEFT(Table1[Flight No Column], 2), RIGHT(Table1[Flight No Column], 3))`
Resolver II

## Re: IF statement with LEN, LEFT & RIGHT formulas

Yes Nippon is right, the issue is your LEN function needs to return a value before it can be compared to 11.

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors