excel question, if cell contains certain wordSolved/Closed

Question

Hello, 

I have a excel spreadsheet that has a bunch of companys with full addresses, phone numbers, etc.  They are all in column A.  I want the information to be horizontal on the spreadsheet.  So all the company names in COlumn a, all the address in column b, phone in c...and so on.  Is there a formula that I can run? 

Please help!!!

venkat1926 · Answer

suppose  data is like this from A1 down to A9 


a 
ad1 
1 
s 
ad2 
2 
d 
ad3 
3 




now in sheet 1 in A1 type (or copy paste this formula) 

=(IF(ROW(A1)<=COUNTA(Sheet1!$A:$A)/3,OFFSET(Sheet1!$A$1,(COLUMN(A1)-1)*3+ROW(A1)-1,0,1,1),0)) 


copy A1 both down and to the right till you get 0 

delete 0s. do you get what you want? 
In sheet1 now you are having 3 sets of data. you can have as many sets as you like but in each set the first is name, second address and third phone no;.

Christin · Answer

that doesn't work...it just copies the data to the second sheet exactly the way it is on the first sheet.

Christin · Answer

I want... 
Every name    Company Name    Address   Phone   Fax   email

in each column...

venkat1926 · Answer

there was a typo in my answer. the formula is to be copied in A1 of SHEET2 repeat sheet 2 . 

I have parked this file )"christin.xls" in the following web page. download the file and see.



http://www.speedyshare.com/files/24385259/christin.xls 


see sheet 1 for data and sheet 2 for results. see the formula in A1 of SHEET2 IN THE FORMULA BAR.

Excel question, if cell contains certain word

4 replies

Subscribe To Our Newsletter!