Excel Problem, need help!! *updated*

[MaDHaVoK]

First Thanks NeoV, you helped me get going in the right direction, although it was exactly what i needed.... I have a new problem, and hopefully last!

I imported a large database and am trying to manipulate the data... here is an example.

This is what i have now. Many many rows with 4 columns. Column 1 is a name. The next 3 colums may or may not have anything in them. What i need to do, is search those 3 columns to see if any of them start with the letter E. Only one or none of them can start with "E". If one of the three columns does start with "E" then I need to copy that cell to a new column in the corresponding row.

Here is my example.. I already have columns 1 - 4, how do i get 5?
__1________2______3_____4______5 becomes
John Doe___Pen____Van___Ear_____Ear
Jane Doe___Ear1___Pen___________Ear1
Smith______Van
Jones____________Ear66_________Ear66
Anand_____Pen5___Oar___Beer
Tech______Ear2__________Van____Ear2


How do i import the right value for column 5???

 

[NeoV]

you can easily do this with an if statement and then a find-replace..

in your second column, just put this formula in =if(a1="*","*","")

That will put an * in your second column on every row that contains a * in the first column.

To get rid of the * in the first column you could just highlight the entire column, then edit-find-*-replace with "", then replace all

 

[MaDHaVoK]

bump

 

[ImNoFun]

Try using a chained If statement with the Left function. =If (left(A1,1)="e",A1,"") + If (leftB1,1)="e",B1,"")........ Now I'm not sure what would happen if you had more than one cell in the row that began with "e".

 

[NeoV]

use this..

=IF(LEFT(B2,1)="e",B2,IF(LEFT(C2,1)="e",C2,IF(LEFT(D2,1)="e",D2,"")))

if what you said is true about no more than one of those columns having a word that starts with E, you will be fine!