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

snd

New Member
Joined
Jan 24, 2010
Messages
44
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.:eek:
Please help me.

Thanks in advance.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,425
Office Version
  1. 2019
#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

New Member
Joined
Jan 24, 2010
Messages
44
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).

<tbody>
</tbody>

<tbody>
</tbody>
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows
You could do a find and replace on '=-'.
 

snd

New Member
Joined
Jan 24, 2010
Messages
44

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,074
Messages
5,835,223
Members
430,348
Latest member
agnes88fe

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top