How to copy, or cut a certain line from a cell?

[petersona01]

Is there a formula to copy or cut a certain line from a cell. Example, can I copy or cut the 2nd and 3rd line of this cell below without copying the name "New Day A";

New Day A
1840 Holman Dr, North Palm Beach, FL 33408
(561) 691-6011

The purpose of this is to drag down the formula for the whole worksheet. I have over 10 thousand cells that I need to remove the address and phone numbers. Thank you in advance.

 

[Excelestial]

<TABLE style="WIDTH: 559pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=745 border=0><COLGROUP><COL style="WIDTH: 236pt; mso-width-source: userset; mso-width-alt: 11483" width=314><COL style="WIDTH: 323pt; mso-width-source: userset; mso-width-alt: 15762" width=431><TBODY><TR style="HEIGHT: 45pt" height=60><TD class=xl63 id=td_post_2881749 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 236pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 45pt; BACKGROUND-COLOR: transparent" width=314 height=60>New Day A
1840 Holman Dr, North Palm Beach, FL 33408
(561) 691-6011 </TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 323pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fcd5b4" width=431>1840 Holman Dr, North Palm Beach, FL 33408
(561) 691-6011 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fcd5b4"> =RIGHT(C8,LEN(C8)-SEARCH(CHAR(10),C8,1))</TD></TR></TBODY></TABLE>

 

[petersona01]

<TABLE style="WIDTH: 559pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=745><COLGROUP><COL style="WIDTH: 236pt; mso-width-source: userset; mso-width-alt: 11483" width=314><COL style="WIDTH: 323pt; mso-width-source: userset; mso-width-alt: 15762" width=431><TBODY><TR style="HEIGHT: 45pt" height=60><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 236pt; HEIGHT: 45pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2881749 class=xl63 height=60 width=314>New Day A
1840 Holman Dr, North Palm Beach, FL 33408
(561) 691-6011
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fcd5b4; WIDTH: 323pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=431>1840 Holman Dr, North Palm Beach, FL 33408
(561) 691-6011
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>=RIGHT(C8,LEN(C8)-SEARCH(CHAR(10),C8,1))</TD></TR></TBODY></TABLE>

Thank you for the quick response. Unfortunetly I got this response after I pasted your formula...."#VALUE!"....Please forgive me, but I'm not advanced in excel just yet....what am I doing wrong? Thanks.

 

[Excelestial]

You must be sure your references are accurate. My formula references cell C8. Where is your data?

 

[Excelestial]

Also...is this complete address in one single cell? You will also receive an error is the data it's looking for (char(10)) isn't there. "Char(10)" is the code for a carriage return within text.

 

[petersona01]

Great, it worked. Now I have a follow up question....is it remotely possible to copy and paste the zip code from all of my cells into a seperate cell? This one might be impossible, but my manager wants to identify metropolitan areas by zip codes and I'm assuming he'll do this through Access, but he wants me to seperate the zip codes and place them into a seperate column. Example;

Alexander City, Alexander City, AL 35010
(256) 234-5348

is there a formula to remove 35010 and place it into a seperate cell?

In addition, is there a way to cut the phone number out of all my cells since I used the "=right" formula already to copy them into a seperate cell? This is a large data base and you will save me days of formating if you can help me. Thank you in advance.

 

[Excelestial]

Certainly. Depending on the real world (if your text strings are consistently spaced)...then use...=MID(C8,SEARCH("(",C8,1)-7,5)...assuming the text is in cell c8

 

[petersona01]

WOW, it worked!....I hope I can be good as you one day. Thanks a ton.