The @ symbol

[GeraldM]

I am importing text into a range of cells then processing it using a VBA
macro to replace particular characters.

However if the imported text starts with the @ character the replacement
routine does not process that cell or subsequent cells.

In this instance, because I control the exported data, I have simply added a
"Space" character before the @ symbol prior to exporting.

However this may not always be an option.

Does anyone have any suggestions on how to force the replace function to
treat the @ symbol as a normal character?

Regards GeraldM

 

[Gary''s Student]

If you first format a cell as Text you can enter a string with @ as the first
character.

If you start your string with a single quote (apostrophe) the first "real"
character can be @

 

[JLatham]

when I formatted the cells receiving text that started with the @ symbol as
Text, I had no problems. Perhaps you can have your workbook set up with the
column receiving the input as Text before distributing it?

I've got to really dig around some, perhaps this will jog someone else's
memory better than mine. If I remember correctly, the @ symbol has something
to do with compatibility with LOTUS 1-2-3 files, and perhaps that's a clue to
a better solution. maybe. somehow.

 

[Gary''s Student]

Enter:
@SUM(1,2)
and Excel translates it into:
=SUM(1,2)

Could this be a spelling correction??

 

[Dave Peterson]

I'm reading this as the import of the data works fine--but subsequently, you
make a change to cells that start with that @ symbol.

And then all heck breaks lose.

Excel does change that leading @ to an equal sign -- even if the cell was
formatted to text before the change. Then it burps if the resulting change
would not have been a valid function.

This seemed to work ok for me:
Select the range to change
edit|replace
what: @
with: '@ (apostrophe @)
replace all

The cells that begin with @ will still look nice in the cell -- but you'll see
the leading apostrophe in the formula bar. The cells that have @ somewhere in
the middle will now look like asdf'@qwer (with that newly embedded apostrophe).

Then do your real edit|replaces. That leading apostrophe will keep the cell
text even after the change.

Then clean up your mess.

Selec the range again (if you have to)
edit|replace
what: '@
with: @
replace all

Excel won't touch that prefixcharacter, but will remove the embedded
apostrophe's. (This can often be a curse. But for you it's a nice feature.)

If you're using a macro, you could include the two extra edit|replaces in your
code, too.

 

[Dave Peterson]

ps. You may find that just changing that @ symbol to something else makes life
a lot easier. (Don't use ', ", or ^ (apostrophe, doublequote, or caret). They
all mean something special to excel.)

 

[JLatham]

Good advice, all. And thanks for reminding me of how @ gets translated in
the 'carry over' from Lotus cell formula protocol.

ps. You may find that just changing that @ symbol to something else makes life
a lot easier. (Don't use ', ", or ^ (apostrophe, doublequote, or caret). They
all mean something special to excel.)

 

[GeraldM]

Thanks Dave, and everyone else who answered.

The offending symbol is entered into a text field of an SQL database.
Unfortunately I am not the originator of the data and have no control of what
is entered. I do however have control of the export (an SQL query). So I am
now testing the first character of the exported field and if it contains a
problematic character (like the @ symbol) I am pre-fixing the data with a
space.

I don't want to change the @ symbol in caseit spoils the meaning of the
original text.

All appears to be working OK now but it did take quite a lot of effort to
discover what the problem was.

Regards GeraldM

ps. You may find that just changing that @ symbol to something else makes life
a lot easier. (Don't use ', ", or ^ (apostrophe, doublequote, or caret). They
all mean something special to excel.)