+ Reply to Thread
Results 1 to 10 of 10

Excel workbook not displaying data starting with zero

  1. #1
    Registered User
    Join Date
    03-22-2018
    Location
    Taunton, England
    MS-Off Ver
    10
    Posts
    35

    Excel workbook not displaying data starting with zero

    Hi there

    I have a strange anomaly with a worksheet. I am pulling data from another worksheet (.xlsx) where all cells are formatted to General and I can "see" bank accounts and sort codes that start with zero without any problems. However, when I import the data in to another workbook - all cells are formatted as General, the cells that should show the sort codes and bank accounts that start with zero are missing. I am completely stumped by this as I thought that this was initially just a formatting issue but other cells that have a zero as the start of a number display fine! I have attached the source data sheet and the workbook that does all the work so you can view the problem. Just need to connect at A2 in Optimy sheet.

    Many thanks in advance

    Tony
    Attached Files Attached Files

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Excel workbook not displaying data starting with zero

    Tony

    Which sheet(s) in which workbook(s) should we be looking at?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    03-22-2018
    Location
    Taunton, England
    MS-Off Ver
    10
    Posts
    35

    Re: Excel workbook not displaying data starting with zero

    Apologies. Source data is in paymenstanon and tab Optimy is in Filtering - this is where the import goes to

  4. #4
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: Excel workbook not displaying data starting with zero

    You say paymentsanon.xlsx is formated as general, but that is incorrect. I open the file and look at sort codes and account column they are formated as text and that is exactly why they are showing leading zero's
    when format is General all bankaccounts and sortcodes that only have numbers will be presented without leading zero's

    So that is where the problems occurs when importing the text columns will change to numbers if the cell format in the new sheet is general. So eighter change the format by default or manage it during import

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Excel workbook not displaying data starting with zero

    Tony

    Is there meant to be a sheet called 'Optimy' in the Filtering.xlsm workbook?

    That's the sheet the formulas on the sheet 'Filtering' in 'Filtering.xlsm' are referring to.

  6. #6
    Registered User
    Join Date
    03-22-2018
    Location
    Taunton, England
    MS-Off Ver
    10
    Posts
    35

    Re: Excel workbook not displaying data starting with zero

    Hi Norie

    The other tab in filtering should be ignored (I should have deleted it).

    Tony

  7. #7
    Registered User
    Join Date
    03-22-2018
    Location
    Taunton, England
    MS-Off Ver
    10
    Posts
    35

    Re: Excel workbook not displaying data starting with zero

    Hi Roel

    I will default all to Text to see if that works - although I thought I had done - it the non anonymised workbook and let you knw in a moment.

    Tony

  8. #8
    Registered User
    Join Date
    03-22-2018
    Location
    Taunton, England
    MS-Off Ver
    10
    Posts
    35

    Re: Excel workbook not displaying data starting with zero

    Hi Roel

    The simplest errors are the ones I miss! Many thanks

    Tony

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Excel workbook not displaying data starting with zero

    Tony

    If there's no data on the 'Optimy' sheet in 'Filtering.xlsm' where is the data meant to come from?

  10. #10
    Registered User
    Join Date
    03-22-2018
    Location
    Taunton, England
    MS-Off Ver
    10
    Posts
    35

    Re: Excel workbook not displaying data starting with zero

    Hi Roel

    So, what I have accomplished is that the source worksheet with the data is formatted to TEXT. The data is then imported to the filtering workbook and the results filtered by date - all works (all cells are formatted General). However, due to the way the data is originally output, there are an awful lot of blank columns. To combat this, the filtered data is copy and pasted (Value, removes the formulae behind the figures) into another worksheet (formatted General) where a macro does the deed of removing the blank columns very well. However, I lose the zero's at the beginning of numbers again! I cannot format the worksheet to TEXT as the macro will not work with TEXT. So, half the battle is won. I am at least getting partial sort code and bank account numbers!

    Is there a way around this in the formatting stage that I am missing?

    Tony

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Displaying Data from multiple worksheets into a master workbook
    By jackee96 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-17-2013, 05:31 PM
  2. [SOLVED] displaying data from another workbook
    By denvernuggets15 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-19-2012, 03:20 PM
  3. Displaying the value of a VBA variable in the excel workbook
    By frogger4 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-18-2011, 11:18 PM
  4. two code starting private sub workbook open in a same workbook
    By ss_bb_24 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-07-2008, 05:54 AM
  5. How do i hide buttons when starting excel workbook?
    By jenniferkognan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-16-2008, 02:40 PM
  6. How do you display a VBA user form when starting excel workbook?
    By dant in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-13-2005, 08:06 AM
  7. Replies: 1
    Last Post: 05-07-2005, 08:07 AM
  8. Starting a MS Excel workbook with a particular worksheet
    By icasaresg in forum Excel General
    Replies: 3
    Last Post: 02-19-2005, 09:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1