[MacTUG] Excel - replacing soft returns update

[Marlon A. Griffith]

Hi Glenn,

 From your solution and some searching, this is the final formula that solved the issue:
"""
=SUBSTITUTE(SUBSTITUTE(A2,CHAR(10),""),CHAR(13),"<br>")

http://ask.metafilter.com/32460/Carriage-Returns-in-Excel
"""

In the example, the soft return is being replaced by an html line feed.

It is clunky (why do you have to resort to vba on the mac, when windows uses an alt key?), but works.

Marlon


On 09/02/12 11:36 AM, Marlon A. Griffith wrote:
> Hi Glenn,
>
> I got your phone message. I have been on vacation since Friday, and thus have not had a chance to look at this issue since. I would like to give you the spreadsheet, but the department that I get it from considers the information very sensitive and thus will not allow me to share it with people outside my team. Thanks once again for the help that you have been able to give me.
>
> Marlon
>
>
> On 02/02/12 10:09 PM, Glenn Anderson wrote:
>> Marlon,
>>
>> Whenever I have to deal with this sort of Excel cleanup, I have always found it best to handle it via a macro. Using a macro gives me much better control over processing the contents of the cells and dealing with processing them.
>>
>> If it is ok, why don't you send me the worksheet and I will see what I can do.
>>
>> On 2012-02-02, at 4:02 PM, Marlon A. Griffith wrote:
>>
>> I found the char() function. You open find:
>> - type 'char(10)' without the quotes
>> - cut it out of the find dialog
>> - press the option/alt key while you paste it back into the find dialog
>> - voila, there is no visible character there
>> - hit find button
>> - however, prompted for all cells with no data 8-(
>>
>> This makes sense but does not solve my problem. I have tried chars 11 and 13 as well with the same result.
>>
>>