trouble with multiple values stacked in a cell using Alt+Enter - who needs a challenge?

[sheriberi]

I don't even know what this function is called, but if you type a value in a cell, then hold down ALT and press enter, you get a new line within the cell. A system I export from (to an XML file then open with Excel) automatically does this. I have 2 problems I am trying to solve that I have with this.

1. In using a V-Lookup to return the values in this cell, the results place everything on one line with a crazy little box symbol in between (I'm assuming its the symbol for "Alt+Enter") I need it to be stacked like the original. See tab "Problem #1" on my attachment. Column B is what the original data looks like that I am using for my VLookup, but column A is the way it returns (after copy/paste special of coursse) and I need it to result like column B. If you click in a cell in column A and press F2 then enter, it fixes it. My problem is I have several thousands of lines of data like this so it isn't an option. I've tried macros and can't get it to work.

2. See 2nd tab named problem #2. Again, I have several thousand of lines of data like this. I need to convert the example data in column A to the example in column B. Basically, I want to delete all but the 1st value, and the cell may contain 1, 2, 3, or 4 vaules all stacked with the Alt+Enter function.

[tigeravatar]

sheriberi,

Welcome to the forum!
You just need to enable the wrap text option. You can do the whole column at once if you want. Just select column A and click "Wrap Text" on the Home tab (you might have to click it twice).

For problem 2, do you want a formula or macro solution? Because this is in the Formulas & Functions section, I'm going to assume you're looking for a formula solution. Using your example file, in cell B2 and copied down:

Please Login or Register  to view this content.

[sheriberi]

Format > Wrap Text...go figure! I can't believe it was that easy. Thank you sooooo much. As for problem #2: formula, macro, I don't care whichever is easiest. Do you know of a macro? I can repost this in that forum if need be.

[sheriberi]

That formula works great though, so heck with the macro. Thanks again, you are a wiz!

[tigeravatar]

You're very welcome

If that takes care of your need, please mark this thread as solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save