How to extract contents of cell with error type "#NAME?" ?

[snd]

I converted a text file to 2007excel file. There was a cell containing this:
=-CLASS FURNITURE
Excel will show it as #NAME?
I need to extract this text "CLASS FURNITURE" so as to concatenate it with adjacent cells (to get a vendor name). I tried many functions (right, text, substitute,..) to no avail.
Please help me.

Thanks in advance.

 

[Special-K99]

#NAME means there is an error.
You need to fix the error before performing functions on it.
=-CLASS FURNITURE is the cause.
Perhaps this should be ='CLASS FURNITURE'

It all depends what you did to "convert" the text file.
You shouldn't need to convert a text file, it should just need opening up in Excel without converting.

 

[snd]

I might've misled you by saying the word convert. I actually used the text import wizard in Excel, then fixed width (not delimited) and so on. That cell just happens to be under a column labeled "Payment Number", which (under the wizard) I set a field width (column break).

Thanks a lot Special-k99 for your prompt response and help.
(Sorry for the table below, I couldn't delete it).
[TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70"][TABLE="width: 528"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Norie]

You could do a find and replace on '=-'.

 

[snd]

You could do a find and replace on '=-'.

Thanks Norie. But it evaluates to #NAME?.
=REPLACE(F9,1,2,"Yeah!"), where F9 is =-CLASS FURNITURE.

 

[Norie]

That's not quite what I meant.

Try going to Find & Select>Replace..., enter =- in the Find what box, leave the Replace with box blank and click Replace All.

 

[snd]

That's not quite what I meant.
.....

That's a good one. Yep, that solved it. "Replace all", what a fine action! Thanks a lot Norie for your help.