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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
#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,286
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,286
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,340
Messages
5,641,576
Members
417,223
Latest member
jelena_

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