+ Reply to Thread
Results 1 to 4 of 4

Some numbers formatted as text

  1. #1
    Registered User
    Join Date
    07-12-2010
    Location
    North Ayrshire uk
    MS-Off Ver
    Excel 2016
    Posts
    10

    Some numbers formatted as text

    some numbers formatted as text

    I have had a problem with some data not appearing to be what I thought it was. A column that appeared to be all numbers contained some text fields.

    I'd been looking for a solution and with the help of members (of another forum) I think I now have a solution.
    If I have a character 1 in a cell - then that cell is 1 character long and has the ASCII code 49 but when I apply isnumeric then I may get TRUE for some cells and FALSE for other cells.

    Not having control of the data source I wanted to be able to validate the data - prior to use.
    So simple but just select the range then multiply the source by 1 and replace

    Please Login or Register  to view this content.

  2. #2
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,062

    Re: Some numbers formatted as text

    not sure but try...
    Please Login or Register  to view this content.
    Regards, John55
    If you have issues with Code I've provided, I appreciate your feedback.
    In the event Code provided resolves your issue, please mark your Thread as SOLVED.
    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

    ...enjoy -funny parrots-

  3. #3
    Registered User
    Join Date
    07-12-2010
    Location
    North Ayrshire uk
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: Some numbers formatted as text

    Hi John55,

    The code I used worked and as I understand by requesting the multiplication Excel converts to a number anything it things could be a number and that's why it should work.

    I'll PM sample code to you.

  4. #4
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,062

    Re: Some numbers formatted as text

    hi,
    I'm sorry! as i said I was not sure! you did not send any code to me!... maybe someone might help you if you upload an attachment.
    type yr text, click "Go Advanced" (see in the right side at the bottom),
    scroll down and find "Manage Attachments",
    "Add Files", select the testfile from yr pc, Done, Close Add Files window,
    Submit Reply

  5. #5
    Registered User
    Join Date
    07-12-2010
    Location
    North Ayrshire uk
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: Some numbers formatted as text

    Hope this one loads (attaches) OK
    The code is very simple but very useful if you are not sure where your data originates!
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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