Excel 2007 : Is there a formula to speed up this process?

**metzgerf** · 03-14-2012, 08:13 AM

Is there a formula that allows you to delete everything after a line break in a cell?

For example, I have several cells that have something that looks like this......

12345
12345

....in the same cell. What I need is this:

12345

(These cells are all in the same column, but it's column B, C and D - and they are merged. In this file, the cell with the numbers in it have several empty cells in between them. For example, the first one would be in cell B14, then there will be several empty cells in column B, and then the next one would be in cell B54, and so on. And like I said, columns B, C and D are merged, so that makes it tricky.)

I know how to remove line breaks, but that just gives me something like 1234512345, and in this case I really can't use an =LEFT or =RIGHT formula because all of my selections aren't 5 digits long (some are 4 digits long, some are 3, etc.) so that wouldn't really be saving me much time. (Besides that, whenever I try to find/replace a line break by typing Alt+010 in the "Find" box, that renders Excel unable to find and replace anything - it gives me an error message telling me that "Microsoft Excel cannot find any matching data to replace," etc., and can only be fixed by closing Excel and opening it again. So that's pretty much out of the question.)

I'm not an Excel expert by any means...All I need to do is remove everything after that first line in the cell (including the line break). Is there a simple way to do this (without creating userdefined functions)???

Thanks in advance!

**JBeaucaire** · 03-14-2012, 11:24 AM

In an adjacent column, you can get the correct value with a formula:

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

or...

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

Your merged cells will cause you no end of grief, I'd stop doing that, I'd take them out if they are there now. To get a "wide" cell, widen the column.

**metzgerf** · 03-14-2012, 12:50 PM

I wish I could do something about the merged cells, but I can't. They come to me this way from a client. If I un-merge them, it screws up the formatting for the whole file.

I have tried the formula that you have laid out, but it doesn't work, probably because of the cells being merged.

Thanks anyway!

**JBeaucaire** · 03-14-2012, 01:18 PM

You can Excerpt 5-10 rows of this file into another workbook and upload here so we can take a look.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

**metzgerf** · 03-14-2012, 02:08 PM

I would love to do that, but the information in the file is classified. Even if I skewed the data but left the formatting, I still don't want to do anything that would put my job at risk. Sorry!

If there's no easy answer, then it's okay....it just seems like something that could be easily fixed in Excel.