What Is This Character? 5

[kc27]

I copied a column from an Excel document and pasted it into Word. The numbers are all followed by a symbol. The symbol does not appear to be live text, because it cannot be highlighted or deleted. Maybe some sort of symbol that Word uses to signify a return? Does anyone know what the symbol indicates?

 

[TonyJollans]

When Excel ranges are copied into Word, they are pasted as table cells by default, and that symbol indicates the end of a table cell (for most purposes equivalent to a return). You see it because you have set formatting marks to be visible, which you can unset by pressing Ctrl+* (or via File > Options > Display tab).

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[kc27]

Thank you for the explanation. I passed the Excel file on to a user who said that character is causing problems for her. I will have to find another way to get her the information from the Excel file without it containing extra characters. For background purposes, the Excel file was created by exporting data from Crystal Reports. Maybe I will need to export it to a different format.

Thanks again.

 

[TonyJollans]

I'd be interested to understand what problems it causes. It isn't really an 'extra' character any more than anything else that might be used to separate individual cell data.

There are all sorts of ways to change the layout, perhaps the simplest being using the Paste Options (default under File > Options > Advanced > Cut, copy and paste > Pasting from other programs), changeable in a particular instance via the (usually irritating) little popup that appears at the end of the data when you paste. Set to "Text Only", it will separate the text from the Excel cells with paragraph marks, returns, call them what you will.



Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[SkipVought]

Quite often there are alternate methods of pasting in some other application.

Right-click may produce a popup menu that may include optional paste methods.

I recently copied some Excel cells and used right-click to find a paste method that eliminated that mystery character separating cells.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[macropod]

Pasting the Excel data into Word as unformatted text will give a tabular layout, using tabs as the column separators and paragraph breaks as the row separators.

You can achieve the same result with an existing Word table by using the 'Convert Table to Text' tool. An advantage of using this method after a simple paste from Excel is that character formatting will be preserved.

Cheers
Paul Edstein
[MS MVP - Word]

 

[MakeItSo]

It is an extra character, Chr(7). Word uses it as cell end character. If you read the text per cell via macro then this will be the last character per cell.
Example:

Dim cel As Cell, ro As Row

For Each ro In ActiveDocument.Tables(1).Rows
    For Each cel In ro.Cells
        Debug.Print (Asc(Right(cel.Range.text, 1)))
    Next cel
Next ro

You will get 7 as output for each cell. So, stripping or trimming that character when reading cell text via macro should suffice.

Cheers,
MakeItSo

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[macropod]

It is an extra character, Chr(7). Word uses it as cell end character.

It's actually a Chr(13) & Chr(07) pair.

If the cell contains only a single paragraph, you can extract the text with code like:

Dim StrTxt as String
With ActiveDocument.Tables(1)
  StrTxt = Split(.Cell(1,1).Range.Text, vbCr)(0)
End With
MsgBox StrTxt

More commonly you'll see code like:

Dim StrTxt as String
With ActiveDocument.Tables(1)
  With .Cell(1,1).Range
    StrTxt = Left(.Text, Len(.Text)-2)
  End With
End With
MsgBox StrTxt

or:

Dim Rng as Range, StrTxt as String
With ActiveDocument.Tables(1)
  Set Rng = .Cell(1,1).Range
  With Rng 
    .End = .End -1
    StrTxt = .Text
  End With
End With
MsgBox StrTxt

In each case the 1,1 represents the row & column address.

Note how when working with the Range object, you only need to strip off one character but, when working with the contents directly as a string, you need to strip off two characters.

When reading whole rows, there is a pair of these Chr(13) & Chr(07) characters per cell, plus another pair for the end-of-row marker.

Cheers
Paul Edstein
[MS MVP - Word]

 

[MakeItSo]

Good point, Paul.

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[kjv1611]

That's really interesting. So would it also be the same as vbCrLf in VBA code, since it's both the CR and the LF, or so I thought?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[MakeItSo]

Nope. This is CR (13) followed by BEL (07) character. vbCrLf is a shortcut for Chr(13) & Chr(10).

Using Split as in Paul's suggestion, you should be on the safe side regardless whether or not only one or both characters are present:

StrTxt = Split(.Cell(1,1).Range.Text, vbCr)(0)
StrTxt = Split(StrTxt, Chr(7))(0)

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[kjv1611]

Man, too many characters out there. I guess I'm just thankful I haven't needed to bother with that one yet. Thanks for the further clarification.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kc27]

Thanks everyone for the replies. Part of the challenge with this task was that I was producing a file for another user to compare against a table in a database. I do not what type of database or how they were doing the oompare. I created the Excel file by exporting a Report from Crystal Reports 9 as MS Excel (97-2000) data only.

When the user's compare failed to recognize obvious matches, she pasted a sample of the Excel content into Word as a troubleshooting tool, and that is when she saw the additional character.

She told me she was struggling with the same issue with another Excel file that someone else had provided to her, so these answers will be very useful.

Thanks again.

 

[macropod]

Word's end-of-cell & end-of-row characters aren't used in Excel, so that's not the issue there.

Cheers
Paul Edstein
[MS MVP - Word]

 

[kc27]

I will have to ask the user how she used the Excel file that I had originally provided. Her theory was that the Excel file contained a hidden character that she could not see until she pasted the Excel data in Word. But from what you are saying, the extra character did not exist UNTIL the data was pasted in to Word.

 

[macropod]

But from what you are saying, the extra character did not exist UNTIL the data was pasted in to Word.

Correct.

Cheers
Paul Edstein
[MS MVP - Word]

 

[intrigrant]

I don't see the problem, it is possible to paste as text from excel to word...... then there are no "end of cell" markers

 

[macropod]

Not seeing 'the problem' doesn't mean it doesn't exist!

If you're doing a simple copy/paste of multiple cells and not seeing the end-of-cell & end-of-row characters, that would only be because you don't have Word's formatting display turned on. If you're pasting as unformatted text, you'll get tabs instead of end-of-cell characters & paragraph breaks instead of end-of-row characters.

Cheers
Paul Edstein
[MS MVP - Word]