[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

How can i specify a Format in a Excel Column

I have a function in VB 6 which opens Microsoft Excel and exports the data of
a Query into the worksheet. All works fine but the problem is that i have a field
which i populate with some credit card nbrs and the shhet displays them as a
5.47464E+15 instead of 5474643171576970

so my question, how can i set the format of a specific cell as well as how can i set this
for a specific column

  • 3
2 Solutions
If you want Excel to ignore the number characteristic of the Credit Card Number put an apostrophe (') on the front of it as you pass it to the worksheet.  Excel then handles it as text


xlSheet.Cells(1,1).Value = "'" & strCreditCardNumber

Sorry, to answer your question, it's not really a formatting issue.  The 'number' has no useful meaning, better to handle it as a string.

The apostrophe doesn't show in Excel by the way.
' set b5

Application.Cells(5, 2).NumberFormat = "################"
Application.Cells(5, 2).Value = "5474643171576970

Actually, following on from Eddy's comment, I think you'd be better to go with ...

Application.Columns("B:B").NumberFormat = "0000000000000000"

A Credit Card number is always 16 digits (at least here in the UK) so you wouldn't want to lose any proceeding zeros, although I doubt any numbers start like that (at the moment!)

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now