Hidden Formating Or Delimiters: Showing Of ?

[Bob]

Hello,

Using Excel 2007 and W7, the 64 bit version.

I received a .csv spreadsheet with data that a particular radio-tuning
program uses when loaded. Works fine.

I created another one of my own, with what I thought were the exact same
formatted columns, etc., but it won't work.

If I append (copy the cells) from my spreadsheet into the one I
received, it, this now composite one, now works.

So, something between the two is different, but not very obvious
(to me, at least).

There possibly is some kind of formatting on the original spreadsheet
that is not obvious.

How would I get the formatting to show ?

I can easily check if some cells are formatted as General, Text, or
whatever.

Rather, how would I display any not-showing delimiters on the one that
works such as any text quotation marks, or whatever, which aren't
showing, or obvious, that might be on the original but not in mine ?

Thanks,
Bob

 

[joeu2004]

I received a .csv spreadsheet [....]
I created another one of my own, with what I thought were
the exact same formatted columns, etc., but it won't work. [....]
So, something between the two is different, but not very
obvious (to me, at least).
There possibly is some kind of formatting on the original
spreadsheet that is not obvious.
How would I get the formatting to show ?

CSV files are simply text files. They are devoid of any formatting
information. In fact, that one of the limitations of CSV files: Excel
reinterprets all of the data, sometimes differently than you intended
originally.

It might help if you explain why you mean by "won't work". What is the
result? What result did you expect?

It might help if you provided two example CSV files, one that works and one
that doesn't work. See the instructions below [1].

Rather, how would I display any not-showing delimiters on the
one that works such as any text quotation marks, or whatever,
which aren't showing, or obvious, that might be on the original
but not in mine ?

It is unlikely that there are hidden characters, much less hidden
"delimiters" per se. But if there are, I would guess that they are
non-breaking spaces (HTML &nbsp), which are sometimes present in text that
is copy-and-pasted from web pages.

Usually, the presence of non-breaking spaces screws things, not make things
work. But without a better description of the data and what "won't work"
means, anything is possible.

The following will count the non-breaking spaces in a cell:

=LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(160),""))

 

[joeu2004]

Rather, how would I display any not-showing delimiters
on the one that works such as any text quotation marks,
or whatever, which aren't showing [...]?

You might use the VBA user-defined function below.

To use (if A1 contains the text in question):

=showHiddenChar(A1)

To create the UDF:

1. Press alt+F11 to open the VBA window.
2. In VBA, click on Insert, then Module to open the VBA editor pane.
3. Copy the text below and paste into the VBA editor pane.
4. You can now close the VBA window.

Note-1: If you want to save the UDF with the workbook (unlikely!), you will
need to do Save As and select the macro-enabled extension (XLSM).

-----

Option Explicit
Function showHiddenChar(s As String) As String
Dim s2 As String, c As Long
Dim hiddenLast As Boolean, i As Long
s2 = Chr(34)
hiddenLast = False
For i = 1 To Len(s)
c = Asc(Mid(s, i, 1))
If 32 <= c And c <= 126 And c <> 34 Then
If hiddenLast Then s2 = s2 & "&" & Chr(34)
s2 = s2 & Chr(c)
hiddenLast = False
Else
If Not hiddenLast Then s2 = s2 & Chr(34)
s2 = s2 & "&CHAR(" & c & ")"
hiddenLast = True
End If
Next
If Not hiddenLast Then s2 = s2 & Chr(34)
showHiddenChar = s2
End Function