Return value based on number of letters in cell

[DOUGMORGAN]

Help required.

The formula i'm looking for is,

If cell A1 contains 3 letters only ,return,"A",otherwise return,"B".

Also is there an easy way to change a positive cell value to a negative,ie 20%
to -20%.

Thanks in anticipation.

Dale.

 

[Guest]

if your text is in A1, put this wherever you want

=if(Len(A1)=3,"A","B")

 

[Jason Morin]

For 3 *characters*:

=IF(LEN(A1)=3,"A","B")

HTH
Jason
Atlanta, GA

 

[firefytr]

question 1:

=if(len(a1)=3,"A","B")

but can be letters or numbers. for only text...

=if(and(len(a1)=3,istext(a1)),"A","B")


question 2:

type "-1" in any empty cell, copy -> paste special, multiply.
use on all cell you want negative.

HT

 

[Harlan Grove]

The formula i'm looking for is,

If cell A1 contains 3 letters only ,return,"A",otherwise return,"B".

First define the following names.

Seq referring to =ROW(INDIRECT("1:1024"))
Alpha referring to =" ABCDEFGHIJKLMNOPQRSTUVWXYZ "

Then try the formula

=IF(SUMPRODUCT(--ISNUMBER(1/(SEARCH(MID(A1,Seq,1),alpha)>1)))=3,"A","B")

Also is there an easy way to change a positive cell value to a negative,ie 20%
to -20%.

Do you want to do so in place? If so, enter -1 in a blank cell, copy it, select
the cell(s) you want to change from positive to negative, then paste special
multiply. If you only need to use them as negative values in formulas, you could
use -ABS(CellRef).

 

[Ken Wright]

2nd bit - Put -1 in a cell, copy the cell, select the range of positive
numbers you wish to change and do edit / paste special / multiply.

 

[Dave R.]

Type -1 into a cell.
Copy it (ctrl C)
select the range of cells you wish to make from positive to negative (ctrl
click or ctrl shift click)
edit>paste special>multiply

 

[Gord Dibben]

Dale

=IF(LEN(A1)=3,"A","B")

Enter -1 in a cell then Copy. Select cell with positive value and Paste
Special>Multiply>OK>Esc.

Note: you may have to re-format the cell to %

Gord Dibben Excel MVP