+ Reply to Thread
Results 1 to 15 of 15

Remove numbering format

  1. #1
    Forum Contributor
    Join Date
    05-14-2015
    Location
    Ulaanbaatar, Mongolia
    MS-Off Ver
    Office 2021 Professional Plus
    Posts
    120

    Remove numbering format

    Hi all, new member here.

    I have problem with a numbering format which i like to remove. I tried all numbering format to remove this but no luck so far.

    Below is the screen shot.

    Numbering Format.JPG

    I would like to delete the spaces between the numbers 1 111 111,00 and remove the last ,00 and make it look like 1111111. Please help. Thank you in advance.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Remove numbering format

    What do you get if you set the number format to "general"?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Contributor
    Join Date
    05-14-2015
    Location
    Ulaanbaatar, Mongolia
    MS-Off Ver
    Office 2021 Professional Plus
    Posts
    120

    Re: Remove numbering format

    Nothing happens.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Remove numbering format

    Are the numbers stored as text strings? Number formatting only affects numbers that are stored as numbers. Text strings that look like numbers are not affected by number formatting. In Excel Options, you can turn on the appropriate error checking options so that Excel will highlight numbers stored as text. Or you can use the ISTEXT() or ISNUMBER() (https://support.office.com/en-US/art...2-7e701b5f785e ) functions to test for this condition.

  5. #5
    Forum Contributor
    Join Date
    05-14-2015
    Location
    Ulaanbaatar, Mongolia
    MS-Off Ver
    Office 2021 Professional Plus
    Posts
    120

    Re: Remove numbering format

    Tried both ISTEXT & ISNUMBER both coming up as FALSE. LOL this is crazy, i have lots of rows and columns like this i dont wanna manually delete all the spaces between numbers and last three ,00 digits. Anyways to fix this? Much appreciate it.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Remove numbering format

    Part of debugging is trying to determine what is going on. Changing number formats will only have an effect if the cell contains a number. Since the ISNUMBER() function returns FALSE, that suggests that what is in those cells is not being seen as a number. However, usually the only other data type to encounter is a text string, and you say that the ISTEXT() function is also returning FALSE.

    What changes if you a) enter 1 in a cell, b) copy that cell to the clipboard, c) select these non-number, non-text cells, and d) Home -> Paste Special -> Multiply. Asking Excel to perform this mathematical operation will usually cause Excel to convert anything to number that it can coerce into a number.

    Can you upload a small spreadsheet rather than a picture?

  7. #7
    Registered User
    Join Date
    04-25-2015
    Location
    Charlotte, NC
    MS-Off Ver
    2013
    Posts
    3

    Re: Remove numbering format

    Do a Find and Replace for " " and replace it with "". Do the same thing with the ,00.

  8. #8
    Forum Contributor
    Join Date
    05-14-2015
    Location
    Ulaanbaatar, Mongolia
    MS-Off Ver
    Office 2021 Professional Plus
    Posts
    120

    Re: Remove numbering format

    How can i upload the spreadsheet that i cant fix?

  9. #9
    Forum Contributor
    Join Date
    05-14-2015
    Location
    Ulaanbaatar, Mongolia
    MS-Off Ver
    Office 2021 Professional Plus
    Posts
    120

    Re: Remove numbering format

    Ah here it is.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    05-14-2015
    Location
    Ulaanbaatar, Mongolia
    MS-Off Ver
    Office 2021 Professional Plus
    Posts
    120

    Re: Remove numbering format

    Anyone? Pls help

  11. #11
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Remove numbering format

    You may want to check your Excel installation for errors. When I tried the ISTEXT() function on columns D and E, it returned TRUE, not FALSE. Examining the text entries in those cells shows that each "number" contains space " " characters. The easiest approach to this may be what msclt suggested:

    1) Do a Find " " (space character) -> replace with "" (empty string).
    2) If comma , is NOT your systems decimal separator, then do a Find "," -> replace with "." (or whatever your systems decimal separator happens to be).
    3) Then use the "multiply by 1" trick (or the VALUE() function, or the unary operator --, or whatever "convert number as text to a number" strategy you like to use) to convert the resulting number stored as text to a number.

    This strategy works for text that has already been imported into Excel. If this is going to be a common thing and you have any control over the process or program that exports these data to Excel, you may make changes in that utility or program so that it will store/export the data in a format that Excel will automatically recognize as a number.

  12. #12
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Remove numbering format

    The "spaces" are actually char code 160's; therefore,
    =SUBSTITUTE(D2,CHAR(160),"")
    Ben Van Johnson

  13. #13
    Forum Contributor
    Join Date
    05-14-2015
    Location
    Ulaanbaatar, Mongolia
    MS-Off Ver
    Office 2021 Professional Plus
    Posts
    120

    Re: Remove numbering format

    MrShorty,

    Tried Step 1 - Didnt find anything ; Step 2 - ,00s were replaced by .00 ; Step 3 - Multiplied by 1 still nothing.
    Capture123.JPG
    Capture1234.JPG

  14. #14
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Remove numbering format

    Did you see ProtonLeah's comment. I had assumed they were regular space characters, but, they apparently are not.

    And one side note on your use of the ISTEXT() function -- Excel usually expects that the argument for the ISTEXT() function will be a single cell or single value. When you feed a range of cells to the istext function it evaluates as false, because the argument is now a range reference, not text. To get an accurate result from the ISTEXT() function, use only one cell =ISTEXT(D2).
    Last edited by MrShorty; 05-15-2015 at 12:32 AM.

  15. #15
    Forum Contributor
    Join Date
    05-14-2015
    Location
    Ulaanbaatar, Mongolia
    MS-Off Ver
    Office 2021 Professional Plus
    Posts
    120

    Re: Remove numbering format

    Thanks MrShorty and ProtonLeah. Problem is now solved. Cheers

+ 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. Cell format for part numbering
    By ranald in forum Excel General
    Replies: 4
    Last Post: 01-12-2012, 07:45 AM
  2. How do I create a numbering format in excel?
    By Colleen in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 03:05 AM
  3. [SOLVED] How do I create a numbering format in excel?
    By Colleen in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 01:05 AM
  4. [SOLVED] How do I create a numbering format in excel?
    By Colleen in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-05-2005, 10:05 PM
  5. How do I create a numbering format in excel?
    By Colleen in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-03-2005, 09:05 AM

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