Find the Position of a Character in Excel Cell

[elaph]

I want a formula to do a LEFT(Text,3) function IF the FOURTH character in the cell is "." and IF it is not give LEFT(Text,4).

[canapone]

Hi,

first attempt

Please Login or Register  to view this content.

or (less "robust" approach)

Please Login or Register  to view this content.

Regards

[davsth]

=IF(MID(a1,4,1)=".",LEFT(a1,3),LEFT(a1,4))

[elaph]

Could you please explain me the logic behind the LEFT function which you first mentioned. It is working well.

[canapone]

Hi

the logic is more or less the same in all formulas

LEFT needs a number: how many characters need to be returned from A2 starting from the left.

In this formula

=LEFT(A2,4-(MID(A2,4,1)="."))

4 is first answer, but formula subtracts 1 if

(MID(A2,4,1)=".") is TRUE

-TRUE is equivalent to -1

If there no "." at the 4th position in A2, MID returns FALSE (zero).

------
Formula from Davsth ( Hi!)

=IF(MID(a1,4,1)=".",LEFT(a1,3),LEFT(a1,4))

If 4th character of A2 is "." (TRUE), formula must extract 3 characters from A2 from the left.

If 4th character of A2 is not "." (FALSE), formula must extract 4 characters from A2 from the left.

Forgive my poor English (and poor explanation too)

Regards