Search A Cell for Certain Letters Then Output a Number
 

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

Search A Cell for Certain Letters Then Output a Number
 

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

"
m wrote in message
...
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

Search A Cell for Certain Letters Then Output a Number
 

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

=IF(LEFT(A1,1)="E",50,IF(LEFT(A1,3)="SPX",100,IF(L EFT(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

On Mar 21, 4:23 pm,
m wrote:
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

Search A Cell for Certain Letters Then Output a Number
 

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

Adam

"Pete_UK" wrote:

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

=IF(LEFT(A1,1)="E",50,IF(LEFT(A1,3)="SPX",100,IF(L EFT(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

On Mar 21, 4:23 pm,
m wrote:
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