How can I make Excel interpret a leading single quote as a regular character?-CodePudding

Let's say I have a CSV file with the following content:

=1 1
--1 1
'=1 1
'hello' world

When I open it with Excel, I can see that the first 2 rows are interpreted as formulas and calculated. While the last two were incorporated with leading single quotes as part of their values:

In this example, a leading single quote was ignored as a

But if only I assign the cells to themselves, the magic disappears:

Range("A3:A4").Value = Range("A3:A4").Value

From now on, the leading single quote is interpreted as a prefix character, and the values are changed (e.g. A4 starts from H, not from ' as before).

This makes hard to work with big data if there are many lines like 'Friends' tv-show. When I assign range values to an array and after processing put them back, I can't be sure that leading quotes (if any) won't be lost, which may cause errors in future.

How can I force Excel to interpret a leading single quote as a part of a string when assigning values to a cell? As you can see from the example above, it's possible to obtain this result. But I can't come up with a simple solution (for example, without writing changed data as a CSV-file and pulling them back with PowerQuery, or prefixing all values whatever they are with ' just to be sure that everything is alright).

I work with MS Office 365, Excel version 16.0

CodePudding user response：

One could argue that it is an Excel design flaw to import this text '=1 1 as plain text without treating the ' as a special prefix, because it is inconsistent with how Excel would behave if you were to enter or paste that text in manually or via VBA. In fact, I don't think there is even a way to insert that text into Excel programmatically or manually the same way that the Import from CSV feature does it.

In any case, what you said: "prefixing all values whatever they are with '" unfortunately looks like the best workaround to me. Now, I can't quite tell exactly what your code logic is supposed to be after that, but instead of this:

destRange.Value = srcRange.Value

which is basically paraphrasing what you wrote above, you could try this (rather clunky) code below instead. That way it wouldn't just put prefixes on absolutely everything, only on things that need them. I'd run this prefix scrub right after the CSV import. To fix it in-place, your srcRange and destRange could be the same range, as you have in your code samples).

If srcRange.PrefixCharacter <> "" Then
    destRange.Formula = srcRange.PrefixCharacter & srcRange.Formula
ElseIf Left(srcRange.Value, 1) = "'" Then
    destRange.Formula = "'"   srcRange.Formula
Else
    'TODO: Deal with other special prefixes?
    destRange.Formula = srcRange.Formula
End If

You may want to enhance it by additionally special-casing other special prefix characters. (And you'd put it in a for-loop around the imported cells)

Doing this right after the CSV import will at least have the desirable side effect of Import/Export consistency as far as those prefix characters go - that is: if you apply this to the imported data, and then export that data back into CSV, the prefixed contents should look the same in the exported CSV as they did in the imported one.

(Note: I'm using .Formula above on the assumption that you actually want to preserve the formulas from the CSV file after the prefix scrub. But if you don't, then using .Value instead of .Formula would work too)

CodePudding user response：

How about?

Dim myValue1 As String
Dim myValue2 As String
myValue1 = "'=1 1"
myValue2 = "'hello' world"
Range("A1").Value = "=" & Chr(34) & myValue1 & Chr(34)
Range("A2").Value = "=" & Chr(34) & myValue2 & Chr(34)
Range("A1").Copy
Range("B1").PasteSpecial Paste:=xlPasteValues
Range("A2").Copy
Range("B2").PasteSpecial Paste:=xlPasteValues
Columns(1).Delete