Return value based on number of letters in cell

  • Thread starter Thread starter DOUGMORGAN
  • Start date Start date
D

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.
 
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
 
The formula i'm looking for is,

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

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%.
Click to expand...

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).
 
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.
 
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
 
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
 
Back
Top