Formatting Number as text Issue.

[DunkUK101]

I am having some problems getting numbers to stay formatted as text and be displayed correctly.

We price products using a pricing tool which exports the results to Excel.
The file is then saved to .xls format; Workbook Attached


When opening these files for editing, we are seeing the barcode numbers as;
7.43216E+11 So we change to Cell Format to Text, but the displayed numbers do not change. The numbers do show in the function bar as digits, but not in the cell.

The only way I can have them display as digits is by formatting to Fraction?! Which seems odd, but then we loose any that start with one or two Zeroes so we’re ending up with 11 or 12 digit numbers when all of the barcode numbers will be 12 or 13 digits long.

Can anyone help or advise?
Opening in Excel 2010. Files exported to, and saved as xls in Excel 2002. (Compatibility mode problem?)

Thanks in advance for any assistance

[judgeh59]

I haven't tested this but I have problems with barcodes if the number doesn't have a single quote at the beginning...it tells excel to force it to text...

hope this helps

Ernest

[ConneXionLost]

Insert this in a module for your workbook:

Please Login or Register  to view this content.

Select the column of numbers, and run the Add_Apostrophe script.

[Pete_UK]

Some of your numbers (e.g. in A12, A19, A21 etc.) are 13 digits long, so how many digits should you have?

If you want to make them up to 13 digits, then you could have this formula in E5:

=REPT("0",13-LEN(A5))&A5

and copy down. You could then fix those values, and then copy/paste them into column A to over-write the ones that are there, and then delete column E.

Hope this helps.

Pete

[DunkUK101]

Insert this in a module for your workbook:

Please Login or Register  to view this content.

Select the column of numbers, and run the Add_Apostrophe script.

Thank you Connexion, sorry but my expertise with Excel is limited, How do I go about adding that code to the workbook?

[Pepe Le Mokko]

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose Tools | Macro | Macros
Select a macro in the list, and click the Run button

[DunkUK101]

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose Tools | Macro | Macros
Select a macro in the list, and click the Run button

The Alt+F11 Shortcut to VB Editor not working for me in 2010, it is in 2003. I have the developer tools in the ribbon visible. Do I enter VBA code via that?

[Kevin UK]

Hi DunkUK101

Developer tab, left hand side, Visual Basic that will bring up the VBE. But ALT + F11 should work!

[DunkUK101]

Hi DunkUK101

Developer tab, left hand side, Visual Basic that will bring up the VBE. But ALT + F11 should work!

Thank you Kevin and others so far with the help.

How odd, my ALT+F11 gives no response at all...and VBE In the developer tab is greyed out?! See attached.

[Pepe Le Mokko]

A greyed-out Visual Basic button indicates that this was not installed when Excel (or Office) 2007 was installed. To install Visual Basic, you need the original installation medium (DVD or downloaded file) for Excel 2007 (or Office 2007) and to use the customize option to select and install Visual Basic for Applications which is located within Office Shared Features

( same for 2010 I guess)

[DunkUK101]

A greyed-out Visual Basic button indicates that this was not installed when Excel (or Office) 2007 was installed. To install Visual Basic, you need the original installation medium (DVD or downloaded file) for Excel 2007 (or Office 2007) and to use the customize option to select and install Visual Basic for Applications which is located within Office Shared Features

( same for 2010 I guess)

Then it looks like I better go find my installation disc! Thank you all for the help!