Conditional Left( extract formula)

[srizki]

Column A has reference ID, that consists of 5 to 15 numbers or alphabets of mix. Most of them are account numbers with extension of invoice number. I am trying to extract account number for future references.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Some account numbers I know that they consist of 8 digits. One of them starts with Y, so if there is Y219417720111210, I want my formula to extract first 8 characters; so far easy, but not all the numbers is column have Y, so I want a formula that should read column A and if the number starts with Y, it should bring first 8 characters otherwise bring everything in it.<o></o>
To clarify further, I am using formula something like this.<o></o>
=IF(A1=”Y*”,LEFT(A1,8),A1), This formula is transferring everything in the cell, ignoring Y.<o></o>
<o> </o>
Thanks<o></o>

 

[Scott Huish]

=if(left(a1)="y",left(a1,8),a1)

 

[Matty]

Does the following do what you want?

=IF(LEFT(A1,1)="Y",LEFT(A1,8),A1)

Matty

 

[Matty]

The "1" on the first LEFT part of the formula I posted is, in actual fact, superfluous. As Hotpepper has shown, if it's the first character you want (which it is), the num_chars element is not required.

Matty

 

[srizki]

Thank you very much.