Problem: Conversion to Txt(Tab Delimited)

Hi All,

I'm working on a data-set from somebody else which has a description column. To import this into our operating system i need to convert to txt (tab delimited) - not usually a problem.

For some reason, whenever i convert this particular file, some of the summary's get broken out with half of the summary being moved on the line below.

I've replaced all line breaks, trimmed cells, removed any text which looks like code incase that was causing issues to no effect. There is no common character which seperates the summaries, some are broken with a " as the first symbol, some a space, some right in the middle of a word. As far as i can tell, there is nothing different about these cells than any of the ones which are converting to TXT without an issue?!

I'm afraid i can't post a sample as its confidential data

Any ideas?! i'm pulling my hair out here!

Many thanks

BB

Select a cell giving the problem and run this:

Sub CheckCell()
   
   Dim str As String
   
   Dim lngLoop As Long
   Dim strMsg As String
   
   str = ActiveCell.Value
   For lngLoop = 1 To Len(Trim(str))
      
      If Asc(Mid(str, lngLoop, 1)) < 32 Or Asc(Mid(str, lngLoop, 1)) > 126 Then
      
         If strMsg = vbNullString Then
            strMsg = "Found:" & vbCrLf
         End If
         
         strMsg = strMsg & "ASC " & CStr(Asc(Mid(str, lngLoop, 1))) & " at position " & CStr(lngLoop)
         
      End If
   Next
   
   If strMsg <> vbNullString Then
      MsgBox strMsg, vbInformation, "Finished"
   Else
      MsgBox "Nothing unusual found", vbInformation
   End If
      
End Sub

It checks for anything that is not printable text like tabs, line feeds, carriable returns, bell(!) - it will list all chars with an ASCII value < 32 or > 126

Hi, wow! Many thanks for the response - can you walk me through exactly what i'm doing with that?!

One of the problem cells is G10 so i copied your script into a blank cell and changed it to read 'Sub CheckCell(G10)' but can't see what or where its doing its thing. I'm by no means advanced at this stuff so i've no idea what an ASCII value is?!

Again, thanks for the efforts!

BB

No idea what you mean by "copied your script into a blank cell". That's VBA code and goes in a standard code module. Do you know how to do that?

Thanks to the power of Google i found the VBA editor - Selected the workbook I need, put in the code and added cell in, gone to run and its asking me to enter a macro?

This is far beyond my already limited excel knowledge so excuse my ignorance!

Cheers

BB

Buddy i'm looking at an excel spreadsheet - clicking 'save as' and saving as a Txt (tab delimited) - no idea what a standard code module is either i'm afraid

OK - go back to the VBA environment (Alt-F11). Click the menu Item 'Insert', select 'Module'. Copy the code in post #2 where the cursor is blinking.

Go back to the worksheet & select 1 of the problem cells. Press Alt-F8 to display the 'Run Macro' dialog. You should see 'CheckCell' in the list, highlight it and click 'Run' (or just double click the name). If you don't see it then check you really have pasted the code intoi a standard code module.

It will respond either with 'Nothing unusual found' in which case that cell should parse OK or a list of control characters (anything not available on the keyboard) found. Most likely these are causing the problem. Once you know what's there, you can get rid of it, but that's another problem...