Solved

excel 201 change format from text no number

Posted on 2013-06-14
11
452 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
Comment Utility
that should be 2010 not 201

thanks
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Columns("A:A").Select
    Selection.NumberFormat = "0.00"
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Or better

Columns("A:A").NumberFormat = "0.00"
0
 

Author Comment

by:Jagwarman
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
Here's an article that should help.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:Jagwarman
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
Did you look at the article I posted?
0
 

Author Comment

by:Jagwarman
Comment Utility
I did but unfortunately it didn't help me

Regards
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
Comment Utility
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
Comment Utility
Thanks Martin brilliant just what I needed.
0
 
LVL 45

Expert Comment

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

Marty - MVP 2009 to 2013
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

My experience with Windows 10 over a one year period and suggestions for smooth operation
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now