So i have a bunch of numbers that was copied and pasted from another document. Heres the problem. I have Vlookup using these numbers as a Lookup value. Vlookup would not work for the life of me but then I discovered if i simply click in the cell (with the numbers), hit enter, the cell error goes away and for formulas work fine.
I need help. The problem is, I have thousands of rows of numbers in the colum. To double click and hit enter will be extremely time consuming. Does anyone know of a quick way to resolve this? I have tried every format to no avail.
Update:
Also, The numbers are 15digits long – If i click the error that says its stored as text and “convert to number”, they appear as 9.902E+14. The formula works, but I need it to stay as the 15 digit.
If i format the cell to a number, nothing works, only doubleclicking and hitting enter in the cell. It appears almost as if excel is not fully recognizing the data until I hit enter
Update 2:
Yes, double click – basically as if I’m editing the cell. Click once to slect, click again to edit – The cursor shows, i hit enter
5
✅ Answers
🥇 Favorite Answer
The quickest way to turn the values into numerics may be to use the “Text to Columns” command. In step 3, when you are given a choice of formats (General, Text, Date, Skip), choose General.
Another way is to use a column that uses the VALUE() function to convert text to numbers, copy the results, and use the “Paste Values” command over the original column.
“So i have a bunch of numbers that was copied and pasted from another document.”
Seems they were pasted as text values, while your VLOOKUP formula needs numeric values.
“Also, The numbers are 15digits long – If i click the error that says its stored as text and “convert to number”, they appear as 9.902E+14.”
That is just formatting, column width to be exact. Just expand the column to see the full number.
“If i format the cell to a number, nothing works”
The cell is formatted for numbers, but still contains a text value. Changing the format doesn’t change the value (i.e. turn text into numbers). This used to trip me up all the time.
Double Vlookup
Source(s): https://shorte.im/bb1Vw
For the best answers, search on this site https://smarturl.im/aDTqy
You can simply highlight column A by clicking on the ‘A’ at the top and go to Format > Cells…., select the Number tab and select ‘Text’. Or here is a method to do as you as simply by double clicking any cell. The following example assumes your data is in column A, as indicated in your question. It will convert the numeric values to text values in column A. Copy the following event handler to the clipboard (highlight the entire code, right click inside the highlighted area, and ‘Copy’): Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim i, LastRow LastRow = Application.Cells. _ SpecialCells(xlCellTypeLastCell).Row For i = 1 To LastRow Cells(i, “A”).NumberFormat = “@” Next Target.Offset(0,1).Select End Sub Select the appropriate worksheet and right click the sheet tab at the bottom. Select ‘View Code’. Paste the event handler into the white editing area to the right (right click inside the area and ‘Paste’). Close the VBE (red button w/white ‘x’ – top right). Double click any cell to format all populated rows in column A.
reverse phone number search compiles hundreds of millions of phone book records to help locate the owner’s name, location, time zone, email and other public information.
Use a reverse phone lookup to:
Get the identity of an unknown caller.
Identify an area code.
Recall the name of a person whose number you wrote down.
Identify an unfamiliar phone number that shows up on your bill.
Are you clicking or double clicking the cell? Your question syntax is conflicting.