Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

excel 201 change format from text no number

Posted on 2013-06-14
11
Medium Priority
?
477 Views
Last Modified: 2013-06-18
I'm using 201 and when I insert a row of numbers some of them change to text. there are over 2000 items in the column. I need some vba code [as this is part of a macro] to change the entire column to number format.

thanks
0
Comment
Question by:Jagwarman
  • 6
  • 5
11 Comments
 

Author Comment

by:Jagwarman
ID: 39247844
that should be 2010 not 201

thanks
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 39247874
Columns("A:A").Select
    Selection.NumberFormat = "0.00"
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 39247877
Or better

Columns("A:A").NumberFormat = "0.00"
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:Jagwarman
ID: 39253493
MartinLiss I tried this but it does not work.

What I am doing is copying from another file then stripping out 7 characters from the cell. In so doing the cell becomes text. I get that little green triangle top left that when you click on it, it says "the number in this cell is formatted as text or preceded by an apostrophy. The when I open that I can convert it to a number. When I do it manually that way it works.

So, is there a way to convert it to a number using VBA. As I said your solution [unfortunately] did not work.

thanks
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 39253528
Here's an article that should help.
0
 

Author Comment

by:Jagwarman
ID: 39253567
Hi MartinLiss.

I found this code but I have to 'manually' highlight the column. Would you be able to make a change to the code so that it works by selecting Column B

Sub ConvertTextNumbers()
 
Dim rUsedRange As Range
 
' Convert all cells with numbers from text to numbers

For Each rUsedRange In Intersect(ActiveSheet.UsedRange, Selection).Areas
rUsedRange.Value = rUsedRange.Value
Next rUsedRange
 
End Sub


Thanks
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 39253570
Did you look at the article I posted?
0
 

Author Comment

by:Jagwarman
ID: 39254087
I did but unfortunately it didn't help me

Regards
0
 
LVL 50

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 39254311
Try this


Sub ConvertTextNumbers()
Dim c As Range

Columns("B:B").Select
For Each c In Selection
    c.Value = c.Value
Next

End Sub

Open in new window

0
 

Author Closing Comment

by:Jagwarman
ID: 39255354
Thanks Martin brilliant just what I needed.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 39256367
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question