Extraneous character

[Guest]

I am using Monarch to find specific data from a report and exporting into
Excel for analysis. However, every field has an apostrophe added in front of
the cell contents. To make it worse, Excel doesn't recognize it, I tried
doing a find and replace, but it didn't find an apostrophe anywhere. I did a
=right(cell, #,#) which works for many of the fields, but the name field is a
variable length. It may seem trivial, but it is affecting how the data is
stored so I can't format or sort the fields correctly.

 

[Ron Rosenfeld]

I am using Monarch to find specific data from a report and exporting into
Excel for analysis. However, every field has an apostrophe added in front of
the cell contents. To make it worse, Excel doesn't recognize it, I tried
doing a find and replace, but it didn't find an apostrophe anywhere. I did a
=right(cell, #,#) which works for many of the fields, but the name field is a
variable length. It may seem trivial, but it is affecting how the data is
stored so I can't format or sort the fields correctly.

Does the apostrophe show up only in the formula bar? Or does it also show in
the cell?

If the former, it is merely a text qualifier.

If the latter, you should be able to get rid of it by using a formula such as

=MID(cell,2,255)


--ron

 

[Guest]

Ron,

Thanks for the reply.

The apostrophe only shows up in the formula bar. When I use the formulas
like: =Left, =Right, or =Mid the apostrophe isn't recognized, so the first
character is omitted.

For example:
The formula bar looks like: '01ES
The cell looks like: 01ES

If I run =Mid(cell, 2, 255) the result is 1ES

Thanks for any help.

 

[Bob I]

Goto Tools, Options, Transition, UNcheck Transition navigation keys

 

[Ron Rosenfeld]

That symbol is merely a text marker -- it marks the contents of the cell as
being text.

It could also be a Lotus left-justified label marker if the transition
navigation keys option is checked.

Excel apparently puts a leading single quote in text cells when populated from
SQL (and perhaps from other sources). But the character doesn't print, and
shouldn't affect anything other than signifying that the data is textual.

It can be removed by several methods.

What is the data like? (i.e. is it numeric to be treated as numeric?, text to
be treated as text? numeric to be treated as text?, etc)?

Ron,

Thanks for the reply.

The apostrophe only shows up in the formula bar. When I use the formulas
like: =Left, =Right, or =Mid the apostrophe isn't recognized, so the first
character is omitted.

For example:
The formula bar looks like: '01ES
The cell looks like: 01ES

If I run =Mid(cell, 2, 255) the result is 1ES

Thanks for any help.

--ron

 

[Ron Rosenfeld]

That symbol is merely a text marker -- it marks the contents of the cell as
being text.

It could also be a Lotus left-justified label marker if the transition
navigation keys option is checked.

Excel apparently puts a leading single quote in text cells when populated from
SQL (and perhaps from other sources). But the character doesn't print, and
shouldn't affect anything other than signifying that the data is textual.

It can be removed by several methods.

What is the data like? (i.e. is it numeric to be treated as numeric?, text to
be treated as text? numeric to be treated as text?, etc)?

Also, if it is a number, is the number more than 15 digits long?
--ron

 

[Guest]

Thanks Bob, but when I went to the options menu, that was already unchecked.
Any other suggestions?

Thanks for the help.

 

[Guest]

The data is mixed. Some are numbers and some are text here is an example:
NAME MRN ACCESSION CULTURE LOCATION REC
Smith, Joe (0000)1111111 02-000-1234 C FUN REF 01A5E 03APR06

Now that you mentioned it is just a marker, the field seems to sort fine,
the date field won't swith to a different format, which is the only problem.
If I try to give a report with a different format people freak out, I guess
it is just too hard to read a date. Thanks for the help, I think I can just
change the date by going text to columns and then putting it back together
using the =right function and omitting the leading apostrophe.

 

[Bob I]

In reading back through your posting, I can't see what the problem is.
The MID function is working as designed. You asked it to start reading
at the second character and the 1 is the second character in the text.

 

[Ron Rosenfeld]

The data is mixed. Some are numbers and some are text here is an example:
NAME MRN ACCESSION CULTURE LOCATION REC
Smith, Joe (0000)1111111 02-000-1234 C FUN REF 01A5E 03APR06

Now that you mentioned it is just a marker, the field seems to sort fine,
the date field won't swith to a different format, which is the only problem.
If I try to give a report with a different format people freak out, I guess
it is just too hard to read a date. Thanks for the help, I think I can just
change the date by going text to columns and then putting it back together
using the =right function and omitting the leading apostrophe.

If you are using the Text-to-columns wizard on the Date filed, there is no need
to use the "right" function. Just using the wizard, and selecting that the
field is "date" and with the correct format (eg.g DMY) will convert the value
from "text" to a number which Excel can interpret as a date (and also remove
the " ' ")


--ron