Convert #NAME? values to Strings

[gelen4o]

Hi,

I have row data that contains serial numbers in the following format "-VPT0000017J". Due to the minus upfront excel thinks this is a formula and assigns an additional "=" to the front. All and all I get an end result of "=-VPT0000017J", which is then displayed as #NAME? making it impossible to format.

Changing the way source data is formatted is not an option so I need a way to get rid of the additional characters.
I already tried using RIGHT; RIGHT+LEN(-2),SUBSITUTE or CTRL + F (Replace). Neither of them worked for me.

I would like a solution that I can turn into a simple VBA, which is to go through the entire column and automatically replace "=-" if needs be.

Any help will be much appreciated!

 

[JackDanIce]

What happens if you remove the "-"? Select the column and use Replace function, does the data then look ok?

 

[Jonmo1]

Try formatting the cell as TEXT Prior To entry

 

[Max1616]

Hello! Easy solution for you:

1. Highlight the column that these errors are in
2. Press control + F (Find and Replace)
3. Click the Replace Tab
4. In the "Find What:" box type: =
5. In the "Replace with:" box type: '
6. Hit Replace All

Let me know if this works or if you need any more help!

 

[gelen4o]

Thank you all for the suggestions !!!
Eventually it took a bit of everything to resolve the issue

What worked for me is as follows:

1. First convert the entire column as text (either source file or destination sheet)
2. Then use CRTL + H to replace "=-" with whatever value or simply leave blank.
It's crucial for the "-" to be removed as this is what has caused the problem in the first place

After that values will appear as text !
VBA and Pivot Tables have no problem working with them