Deleting or Cutting a Section Out of a Cell

[petersona01]

Is there a formula to cut out a section in a cell? Example, can I cut out everything except for the title "West Tx Counseling & Rehab"

...It will look like this before the formula;

West Tx Counseling & Rehab
1808 Market Center Blvd, Dallas, TX 75207 » Map
(214) 630-7146

....And like this after the formula;

West Tx Counseling & Rehab



Thank you in advance.

 

[wigi]

Is this all in 1 cell?
What is the logic to take out pieces from the cells? Is it everything until the first "Alt-Enter" in the cell?

 

[petersona01]

With the help of others on this board I've been able to format my 10 thousand + cell database to look like this below


Full info Address Zipcode Phone Number


What I want to do is remove the "full info" and just leave the name so it reads like this below


Name Address Zipcode Phone Number



another example is

Substance Abuse Svc Norfolk
7460 Tidewater Dr, Norfolk, VA 23505 » Map
7460 Tidewater Dr, Norfolk, VA

I would like to get rid of everything except "Substance Abuse Svc Norfolk"

The goal is to have a formula to copy and paste it in a cell next to it and then drag down. Thanks

 

[petersona01]

Bump....Someone please help, I have a deadline and if I format each cell (there's over 10 thousand) it will take me days. i just need to seperate the title in each cell. Thank you for your help....I really appreciate it.

 

[wigi]

Bump....Someone please help, I have a deadline and if I format each cell (there's over 10 thousand) it will take me days. i just need to seperate the title in each cell. Thank you for your help....I really appreciate it.

Again, what is the logic - plain English - for the formula? If you can't define the logic, the formula will not work very well I'm afraid.

Is there an Alt-Enter in the cells?
Is it everything until the first number in the cell? Or the first time a 4-digit number occurs? Are there numbers in the part you want to extract? And so on.

Please do not bump after 1 hour, that's not really the way to go. Helpers are voluntary and spend a lot of free time to help many many people, just like you.

 

[petersona01]

Sorry, I thought you forgot about me, or just moved on. As for the logic, I'm not sure.... I guess it would be "everything until the first number in the cell." Thanks for giving me options, I really didn't understand the "logic" question at first.

 

[Peg F]

I found a video by Bill Jelen that may be helpful.

http://video.answers.com/mrexcels-learn-excel-split-alt+enter-108643843

It takes multiple lines in 1 cell and splits the data back out to columns. Rather than try to pull just the 1st line, I'd use this method and then delete the columns you don't need.

Hope this helps.

Peg

 

[Michael M]

If the lines are sperated by ALT + Enter
try

=LEFT(A1,FIND(CHAR(10),A1,1)-1)

and copy down as required

 

[petersona01]

Michael M, your formula worked....thank you all who helped me, you guys rock!!!!