Activate cell

I have a large spreadsheet with 100,000 rows and in one column we have variations of numbers and text numbers.  We formatted the whole column as text to enable vlookup to another worksheet for a column of similarly formatted combinations, formatted as text.  The primary column needs every cell of text activated.  We use F2 then enter on every cell.  This is tedious and we need another way to activate all these cells to acknowledge the text, so the Vlookup will work properly.
Please help.
AqusenuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieVBA ExpertCommented:
Do you mean you want to convert a column of text to numbers?
0
AqusenuAuthor Commented:
No.  we have a column of combination numbers and text with numbers we formatted as all text.  The format is not activating the cells.  We must click into every cell with a mouse or F2 to then click enter to activate the cell.
0
Saqib Husain, SyedEngineerCommented:
Try

Select a blank cell
do a copy  (Ctrl-c)
Select your data
Do a paste-special with the "Add" option
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Saqib Husain, SyedEngineerCommented:
or

select your data
ctrl-c
pastespecial with the "Values" option
0
AqusenuAuthor Commented:
There are 100,000 lines to do this for. Looking for something more automatic like VBA script or macro or other excel function that will work.
F2, enter is the fastest right now, but 100,000 lines is going to take a few days.
0
Saqib Husain, SyedEngineerCommented:
select your 100,000 lines data
ctrl-c
pastespecial with the "Values" option
0
AqusenuAuthor Commented:
the simple thing, I think, would be a macro, to select the cell, f2, enter, then move down 1 cell.  Not sure how to write this exactly
0
Saqib Husain, SyedEngineerCommented:
You do not need VBA for this. Just select your entire data and do as I have spelled above.
0
Rob HensonFinance AnalystCommented:
Alternative, in a spare column add the following formula, where values are in column A:

=IF(ISNUMBER(A1),TEXT(A1,REPT(0,LEN(A1))),A1)

Copy down the required rows and then copy > paste special > values into the original column, overwriting the mixed values.

Thanks
Rob H
0
NorieVBA ExpertCommented:
So  you do want to convert text to numbers, but not the entire column?
0
Rob HensonFinance AnalystCommented:
Another alternative, allow for the mix of text and numbers in your vlookup:

=IFERROR(VLOOKUP(A1,Range,Offset,Type),VLOOKUP(VALUE(A1),Range,Offset,Type))

Thanks
Rob H
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AqusenuAuthor Commented:
Thanks, this solved it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.