Forum Discussion
Solved
Copy/paste value from formula leads to failed vlookup
I am trying to copy and paste values from a formula data set (RIGHT(,5) - get zip codes from addresses) into a separate sheet to then perform a vlookup. The vlookup returns with #N/A. The pasted valu...
If you apply =RIGHT("abc12345",5) it returns text "12345". Copy/paste as value also paste text. As variant you may use =--RIGHT("abc12345",5) to return numbers as numbers.
Please try to wrap the lookup_value argument with VALUE function.
utilizing a value function does work, thank you. I am still curious as to why the post paste special values formatting does not allow for a successful vlookup without adding a value function to the formula (annoying to need to add this to all future formulas).
Is there a solution or setting with the paste special values that would not require the value function in the lookup?
Is there a solution or setting with the paste special values that would not require the value function in the lookup?
- After the initial paste, perform another paste like this:
1. Enter 1 in a cell outside the paste area.
2. Select the cell with 1 in it and press Ctrl+C to copy it to the clipboard.
3. Select the paste area.
4. Press Ctrl+Alt+V,M,Enter.
5. Press Esc.
6. Delete the content of the cell you entered 1 earlier. If you apply =RIGHT("abc12345",5) it returns text "12345". Copy/paste as value also paste text. As variant you may use =--RIGHT("abc12345",5) to return numbers as numbers.
- I like the simplicity of this option. Thanks! Works like a gem and doesn't impact additional formulas/cells.