[Solution] adding =substitute formula to an existing formula reformats cell value from percentage to plain number

adding =substitute formula to an existing formula reformats cell value from percentage to plain number

Hi there,
I have a formula which brings sales upsell percentages from a seperate sheet (copied in from an sql database table) into the main page of my sales kpi worksheet.
As you may know, missing data in sql table fields can sometimes appear as "-" or "NaN" and sometimes
"Infinity" given certain circumstances.

Using a formula like: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(VLOOKUP(A1,Sheet2!$A$1:$B$2,2,false),"NaN",0),"-",0),"Infinity",0) usually sorts this out, meaning a vlookup would return 0 instead.
No worries.
The problem comes from adding this type of formula to an exising =IF(ISERROR formula which corrects missing sales agent names where #N/A would usually be returned.
When I try to incorporate an =substitute formula to the existing =IF(ISERROR formula, this
is when the cell values get reformatted as a plain number rather than a percentage.
(Eg.
49.12% would be reformatted as 0.4912)
My formula as it stands:
=IF(ISERROR(VLOOKUP(B2,Feed9!$A$2:$AG$127,46,false)),0,VLOOKUP(B2,Feed9!$A$2:$AG$127,46,false))
This returns either the data im after, or zero if nothing is found.
Great.

However, if there are results other than the data im after (such as "-" "NaN" "Infinity" etc, mentioned above) this returns a #VALUE error.
Ive tried different ways of adding an =substitute formula, and each time it reformats the cell values from percentage
values to simple numerical values.
This is what ive tried so far:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(VLOOKUP(B2,Feed9!$A$2:$AG$127,46,false)),0,VLOOKUP(B2,Feed9!$A$2:$AG$127,46,false)),"NaN",0),"-",0),"Infinity",0)
This returns the correct data, but expressed as 0.4912 rather than 49.12%
Any ideas how this can be avoided?
- Please excuse any syntax errors, formulas typed from memory -

Keys to the Problem adding =substitute formula to an existing formula reformats cell value from percentage to plain number

Download SmartPCFixer to Fix It (Free)

First off, I would replace your IF(ISERROR(), .., ...) with the IFERROR(..., ) available in Excel since Excel 2007.
This drastically reduces the length of the formula and provides for a default value.
Then use a double unary (e.g.
double minus or --) to attempt a conversion to a true numerical value.
The text values that look like percentages should convert to their numerical values (e.g.
0.4912) and anything else should throw an error, resulting in the default from IFERROR().
=IFERROR(--VLOOKUP(B2, Feed9!$A$2:$AG$127,46,FALSE)), 0)
Format the results as Percentage with two decimal places and you should have real numbers that you can work with.
Note that IFERROR() was introduced with Excel 2007 and if you need to provide compatibility with version of Excel prior to that this proposed solution may not be appropriate.

Running System Update Readiness Command

On running the System Update readiness Command, it will Detect & Replace Incorrect Registry data with the Correct one. Which may ultimately Solve adding =substitute formula to an existing formula reformats cell value from percentage to plain number in Windows.

1. Open CMD as Administrator by Simply Right Clicking on Start button > Command Prompt (Admin).
2. Now Type or Copy paste "DISM.exe /Online /Cleanup-image /Scanhealth" and hit Enter,
3. Now again Type or Copy paste this "DISM.exe /Online /Cleanup-image /Restorehealth" and hit Enter.
4. Close the Command Prompt Window because we are done.

Try and see if you still get the same error adding =substitute formula to an existing formula reformats cell value from percentage to plain number in your Windows.

Note: You may be prompted to enter your administrator's password when you try to execute certain system tasks!

Another Safe way to Repair the Problem: adding =substitute formula to an existing formula reformats cell value from percentage to plain number:

How to Fix adding =substitute formula to an existing formula reformats cell value from percentage to plain number with SmartPCFixer?

1. Click the button to download Error Fixer . Install it on your system.  Open it, and it will perform a scan for your system. The junk files will be shown in the list.

2. After the scan is finished, you can see the errors and problems need to be fixed. Click Fix All.

3. The Repair part is finished, the speed of your computer will be much higher than before and the errors have been fixed. You can also use other functions in SmartPCFixer. Like dll downloading, windows updating and print spooler error repair.

Related: How to Fix - 64g ssd with a 500g regular drive?,Allow Unhide Rows in Protected Workbook [Solved],[Solved] Get in Excel 2007 data from Access 2007 out of self-built Queries,[Solution] How can I temporarily disable 'service manager' to install Adobe flashplayer?,[Anwsered] When I try to watch a flash video, I am told occasionally that I don't have Adobe Flash.,Solution to Error: Black screen during boot sequence,[Solved] Can't restore Windows 7 64-bit from external hard drive,How to Fix - IE 11 Enhance Protect Mode reset issue with add-on's?,Solution to Error: Internet Explorer 9 update/install error - Error Code 80092004,Upgrading to IE 8 causes cookies to get deleted when starting IE [Anwsered],Solution to Problem: All programs try to start from windows component
,Troubleshoot:External Hard Drive not listed in Windows 7 backup wizard Error
,How to Fix Error - Getting an error "not connected to the internet" while trying to install Samsung Kies?
,How to Fix - Internet Explorer shuts down and reopens tab when attaching to email or uploading files.?
,Fast Solution to Problem: Sending Error Message
,[Anwsered] Thinkpad 8611 Boot,How to Resolve - Svchost Helper?,Fast Solution to Problem: L30 101 Driver Windows 7,Troubleshooter of Error: Io Device,How to Fix Error - Dell Laptop Code 39?
Read More: Fast Solution to Error: Adobe flash player states its not installed.,How to Resolve - After I use an Excel 2007 data list sheet for a mail merge, I get an error message when I try to open the list and can never open the spread sheet again?,After closing laptop lid, computer screen won't turn back on when re-opening it and pressing power button. [Anwsered],Troubleshoot:Adobe 9 Pro activation,[Solved] After IE 9 download - I cannot access a favorite web site,a file called mDNSResponse.exe. is causing bonjour not to operate properly,what should I do?,A QUESTION USING THE "IF'S" Formula.,A continuos flashing window with which title is C:Windows\System32\cmd.exe, and has the following message: The syntax of the command is incorrect.,Acrobat compatibility issue and you tube problems____,ActiveX on IE 9 not loaded