Ignorning #NAME? error

S

spr1nt

i have data cells that look similiar to this:

- Jack Nap
- Steve Lee

and excel replaces those text with #NAME? (invalid error name). im
assuming its doing this because of the - sign in front of the name. is
there a way i can force excel to ignore the error and show the text?
thanks in advance.
 
D

daddylonglegs

Try putting an apostrophe (single quote) in front to signify text, e.g.

'- Jack Nap

or you could preformat your cells as text, then you can just enter

- Jack Na
 
A

ANdras

Hi there,

you're right, it's because the - sign.
to avoid it, either you can start the content of the cell with shift+1
(which is: ' ) then the excel does not think, you are going to give formulas
in it, then stops calculating
or
you select the desired range of cells and a) right click/Format Cells or b)
Format menu/Format Cells in both cases select the Number sheet and set the
format to Text and then you can add the requiered texts

in both cases you tell Excel that you are not using formulas

NB. in case later on you want formulas in the formatted cells, then you
should do the other way aroind, to have active formulas...

Best regards,
ANdras
(Hungary)
 
S

spr1nt

thanks for the help guys. i have a couple more questions im hoping you
can help me out with. is there a way to make excel treat everything as
a text before loading a file? right now i have to fix every single cell
with the error after opening the file.

i also tried selecting the whole column with the errors and choosing
format cells --> text hoping that it would change the #NAME? to the
correct text but this didnt work. am i doing something wrong or is
this not possible?
 
D

Dave Peterson

If you're opening a text file, make sure the file does not have an extension of
..csv (use .txt).

Then when you do File|Open, you'll be presented with the text to columns
wizard. You can choose text for each field you want.
 
Top