If Right Left Query

[ExcelBob]

How can I return the first 6 numbers of a cell if the 7th number in the cell equals 1 and return all 7 numbers if the seventh cell is greater than 1

ie 1234561 would be 123456
ie 1234562 would be 123456-2
ie 1234563 would be 123456-3

thank you kindly

[daddylonglegs]

Try

=LEFT(A1,7-(RIGHT(A1)=1))

If you want a - in there too

=LEFT(A1,6)&IF(RIGHT(A1)=1,"","-"&RIGHT(A1))

[ExcelBob]

Thanks daddy but it's failing on the =1

even if I put >1 it does not work.

for some reason it is not looking at the 7th digit and recognising it as equalling 1 (or greater than 1 when in input >1)....

Any more ideas greatly appreciated

Thank you very much

[daddylonglegs]

see tony's solution.....or my revision..... Because RIGHT function returns text

=RIGHT(A1) will never equal 1, it will be "1"

[ExcelBob]

Legends the two of you!

Many Thanks for the solution

Have a GREAT DAY!

[tony h]

=LEFT(B3,6) & IF(RIGHT(B3,1)>"1","-" & RIGHT(B3,1),"")

assuming data in B3 and length is 7

hope this helps

[daddylonglegs]

tony's post reminds me that the result of RIGHT function is always text, so for my suggestions to work they'd need to be

=LEFT(A1,7-(RIGHT(A1)="1"))

and

=LEFT(A1,6)&IF(RIGHT(A1)="1","","-"&RIGHT(A1))

...serves me right for not testing

[tony h]

Daddy long legs:

I do like the sneakiness of you first solution. It didn't occur to me.

regards