INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

What Is This Character?

What Is This Character?

(OP)
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?

RE: What Is This Character?

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

RE: What Is This Character?

(OP)
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.

RE: What Is This Character?

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

RE: What Is This Character?

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: What Is This Character?

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]

RE: What Is This Character?

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:

CODE

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.

RE: What Is This Character?

3

Quote:

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:

CODE

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:

CODE

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:

CODE

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]

RE: What Is This Character?

Good point, Paul. thumbsup2

"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.

RE: What Is This Character?

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

RE: What Is This Character?

2
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:

CODE

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.

RE: What Is This Character?

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

RE: What Is This Character?

(OP)
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.

RE: What Is This Character?

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]

RE: What Is This Character?

(OP)
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.

RE: What Is This Character?

Quote:

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]

RE: What Is This Character?

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

RE: What Is This Character?

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]

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close