Text to Columns, Delimit by Paragraph

[sweetmetrics]

What I have:
I have a .csv file that has a column that contains 4 paragraphs of text within each cell. When "wrap text" is selected, an individual cell appears neatly formatted. I have roughly 300 rows of these paragraphs (300R x1C)

What I am trying to do:
Remove paragraphs 2 & 4 while preserving the 'formatting' so the new cell has just paragraphs 1 & 3. I need it this way because I am uploading this .csv file back into an e-commerce store database. These sets of paragraphs are actually product descriptions of products for sale on a website.

What I've tried:
Text-to-Columns-> Delimiter=paragraph -> delete the un-needed cells then concatenate back together. Unfortunately I have failed. I haven't been able to properly separate the cells. I also am not sure how I would concatenate them back together properly either.

Thank you in advance for your help!

I am using Excel for Mac (2011) with a .csv output file required.

 

[ChrisM]

This can be done, but unfortunately I've run out of time to keep messing with it. There are formulas to find the Nth occurrence of text in a string (search this site for them). All you need to do is find "AA", the first occurrence of the line feed character (end of paragraph one), then the 2nd "BB" (start of paragraph 3) and 3rd "CC" (end of paragraph 3) occurrence.

Once you know these three points you can build a concatenated string in a neighboring column: = paragraph1 & VBCRLF & paragraph2

Paragraph1 would be pulled from: = LEFT(A1, "AA" - 1) AA here is really a character count

Paragraph2 would be: =MID(A1,"BB"+1, "CC" - "BB")

Hope this is making sense!

Here is how to find "AA":

=FIND(CHAR(10),A1,1) that will give you the position of the first line feed

Sorry, that's all I can do at this point!

 

[sweetmetrics]

ChrisM,

Thank you very much! This is definitely a great step in the right direction, I definitely follow the logic behind your process. There's only one problem, the "=FIND(CHAR(10),A1,1)" returns a #VALUE and it must be because there is no "new line" character within these cells with multiple paragraphs.

My question to you (or anyone else that is perusing this thread ) is can I somehow figure out what ASCII character actually separates the paragraphs within these cells? Once I know that, the rest of your solution will work great.

Thanks again Chris!

 

[ChrisM]

You just need to google for ASCII codes, like here:

http://www.asciitable.com/

You could try code 13, that might do it.

There other way is the brute force approach. Create a formula in Excel that points to your paragraph cell. Now you need to guess the character position of the line feed, then do this:

=CODE(MID(A1,6,1))

where the number 6 is the character position you think the line feed occurs at. Just count the characters in your first paragraph, say there's 100, then put 101 in the formula above and see what you get. Then try 102, 103, etc, until you get to the first character in your second paragraph.

 

[sweetmetrics]

ChrisM,

Thank you very much for your follow up. With your extra help, I just got everything to work beautifully!!