Search A Cell for Certain Letters Then Output a Number

[Guest]

I have a column that has cells containing text. These cells could be in
three different formats. If they start with E, I want to output 50. If they
start with SP, I want to output 250. If they start with SPX, I want to
output 100. Any suggestions would be much appreciated.

Thanks

Adam

 

[Bernard Liengme]

Hi Adam:
Try this
=IF(LEFT(A1)="E",50,IF(LEFT(A1,3)="SPX",100,IF(LEFT(A1,2)="SP",250,"what to
do elsewise?")))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"(e-mail address removed)"

 

[Pete_UK]

Assuming your data starts in A1, try this in B1:

=IF(LEFT(A1,1)="E",50,IF(LEFT(A1,3)="SPX",100,IF(LEFT(A1,2)="SP",
250,0)))

and copy down the column. This will output 0 if none of the conditions
are met.

Hope this helps.

Pete

 

[Guest]

Thanks a lot for your help guys. That worked perfectly.

Adam