How about a VB macro in Excel?
Select the cells you want to replace and create and run the following subroutine on them:
Cells.Replace what:=vbCr, replacement:=";"
Cells.Replace what:=vbLf, replacement:=";"
Cells.Replace what:=vbCrLf, replacement:=";"
From here.
Also, why can’t you just download the file from your office PC onto your Mac, do the work, and re-upload it?
Or maybe do a copy & paste across the RDC session?
And according to Microsoft, you can use special characters (including ASCII/ANSI codes) in Word’s find and replace.
Excel for Macs is slightly different from Excel 2003. I’ve found that emailing files back and forth is a hassle. Copy & Paste won’t work, because alt codes don’t work in it. (I’ve tried.)
I don’t know how to make a macro in Excel (or in any other program).
What about find & replace in Word?
Can you just email me the actual data and I’ll do it for you and give it back to you as a comma-separated file (csv) that any version of Excel or Access will be able to access?
If not, which version of Windows and Excel do you have on the office PC? First try Googling for instructions (it’s pretty much just selecting the cells, finding the Macro button in your version of Excel, creating a macro with a random name and then pasting the snippet between the auto-created subroutine beginning and end, then clicking “run” to perform the macro on the cells). If you still can’t figure it out, let us know and maybe we can walk you through it. Hopefully your company won’t have restricted macros.
I haven’t tried importing the file into Word. Might give it a try. We’re using XP and Excel 2003. (Non-profit company in hard economic times.)
So I find the macro button, copy the code you posted before, name it, save it, highlight the column, and the Run?
I couldn’t send you the file, but I can send you the sample file I sent to Noone Special. That just has the header and one data record, with fake information in it but the NDC intact. Would that work?
As I said, they’ve cracked down on Internet usage. I’m in the office tomorrow, so I wouldn’t be able to come here for instructions and apply them to the file until Thursday.
I was going to ask you to send the source file, if you could, for the sample – that is, the data records’ file that you initially fed into Excel, rather than the *.xls which Excel creates from it – since the *.xls file is mostly binary (which will make wholesale replacement of any character a dangerous endeavor); I’m going to say it would be best if you could send the data record to Reply as well.
Editing binary files is, frankly, a bitch…
You have to create the macro and name it before you can edit it and save it, but yes, that’s essentially it.
A sample file would only be useful if you can exactly re-create the actual file’s data structure, which may be hard if you can’t tell for sure if the character is a CR. Besides, with a sample file, the best we could do is mail you a pre-made macro or program that would (hopefully) do the right thing with the actual data. Copy & pasting the VB code would be just about as easy.
I’d try fiddling with the macro from home, where you have internet access, instead of in the office. That way you can keep Googling or asking here for help if you need it.
And what kind of tech firm disallows Internet usage for work-related purposes?
It’s very definitely a CR – at least, the Non-printing char in the demo file I was sent is very definitely a CR.
After saving the file from Excel as a CSV, I was able to find and replace the CR in Word 2007 on Windows – specifics already sent to Johnny; hopefully he has a version of Word that will allow close enough functionality to do a “replace all” on an exported CSV that can then be re-imported.
Hey, everyone.
I received Noone Special’s email, in which he said he was successful using Reply’s suggestion of copying the file into Word.
Success!
Thank you, Noone Special and Reply. I now have a new tool I can use when this data comes in each month, and for any others that may be like it. My coworker and my boss say my ‘imaginary friends’ are awesome. I agree.
Nice 
Man, Johnny, don’t you just miss TSO/ISPF with all the Mickey Mouse workarounds you have to use on PCs, Macs, Unix, Linux, and assorted stuff.
(There is a PC version if SPF, but we can never get management to pony up themoney to pay for it.)
There are many times I wished I was on TSO/ISPF. In this case I could just display the file in text, find the hex code, and write an Easytrieve to change the hex code to whatever I want. (Or else just parse the field for it and write what follows to the output position.)