Bug?
 

am am trying to use the following code in a cell:

=LEFT(Sheet1!S1,2)&left(x,2-LEN(sheet1!S1))

it's designed to extract the first two characters from S1 on sheet1, but if
there are not two characters there, to use the first two from a cell named
'x'.
It works fine when the target cell starts with alphas, but fails if the
target cell has nemerics for the first 2 chacters. Appreciate any guidance.

Bug?
 

Try this:

=IF(LEN(Sheet1!S1)=2,LEFT(Sheet1!S1,2),LEFT(x,2))

Mike

"General" wrote:

am am trying to use the following code in a cell:

=LEFT(Sheet1!S1,2)&left(x,2-LEN(sheet1!S1))

it's designed to extract the first two characters from S1 on sheet1, but if
there are not two characters there, to use the first two from a cell named
'x'.
It works fine when the target cell starts with alphas, but fails if the
target cell has nemerics for the first 2 chacters. Appreciate any guidance.

Bug?
 

Not sure what "fails" means to you, but if S1 contains more than 2
characters, the second part of your formula will return a #VALUE! error,
since you can't take a negative number of characters with LEFT()\

Seems to me you could use

=LEFT(Sheet1!S1&x,2)



In article ,
General wrote:

am am trying to use the following code in a cell:

=LEFT(Sheet1!S1,2)&left(x,2-LEN(sheet1!S1))

it's designed to extract the first two characters from S1 on sheet1, but if
there are not two characters there, to use the first two from a cell named
'x'.
It works fine when the target cell starts with alphas, but fails if the
target cell has nemerics for the first 2 chacters. Appreciate any guidance.