+ Reply to Thread
Results 1 to 3 of 3

Numerical format problem

  1. #1
    Registered User
    Join Date
    09-05-2015
    Location
    hk
    MS-Off Ver
    2007
    Posts
    2

    Numerical format problem

    i have many records that saved seconds into one field
    the fields will record 4 range of seconds like:
    [these kinds of record are only saved in one field]

    BA

    22.78 22.87 23.98
    or
    22.78 22.87 23.98 24.89
    or
    22.78 22.87 23.98 21.54 25.68
    or
    22.78 22.87 23.98 21.54 25.68 29.68

    Usually I will use formula =LEFT(BA1,5) and =MID(BA1,7,5) , =MID(BA1,13,6)to records down to other fields like

    BB BC BD
    [22.78] [22.87] [23.98]

    there is no any problem with these records but sometimes I mark a record like

    24.2 23.43 23.22

    Since these records are not the same as the upper records because these kinds of field do not accurate to 2 decimal places
    If i still use formua left or mid , it will disappear few numbers like"

    24.2 3.43 3.22

    I have tried to use [Storage Format]> [custom] and added a new type #.00

    #.00 could help me to add a 0 after the accurate to 1 decimal place like 24.20

    but it could not use in a field which had used formula LEFT and MID

    Only the field which did not use formula could use the custom #.00.

    Now I have no idea to add a zero after the decimal place

    Any one have some suggestions for me?

    Thank you !

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Numerical format problem

    I think you trying to separate them into different columns. If that's the case you can use Text to Columns feature in excel or use formula

    inter formula in BB1 and pull it to the right until you see blanks

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    BA
    BB
    BC
    BD
    BE
    BF
    BG
    1
    22.78 22.87 23.98 22.78 22.87 23.98
    2
    22.78 22.87 23.98 24.89 22.78 22.87 23.98 24.89
    3
    22.78 22.87 23.98 21.54 25.68 22.78 22.87 23.98 21.54 25.68
    4
    22.78 22.87 23.98 21.54 25.68 29.68 22.78 22.87 23.98 21.54 25.68 29.68
    Sheet: Sheet5
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Numerical format problem

    TRY

    in BB2

    =IFERROR(TRIM(MID(SUBSTITUTE($BA2," ",REPT(" ",99)),(COLUMNS($A:A)-1)*99+1,99))+0,"")

    Copy across

+ 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. Give a letter a numerical value and use it to format another cell
    By fazthfc in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-22-2014, 07:34 AM
  2. Can I format a numerical cell to show as textual?
    By skiwoods in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-08-2013, 10:32 PM
  3. Format a range of cells to numerical values.
    By AnthonyWB in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-06-2011, 03:14 PM
  4. Numerical values in a text format
    By mcinnes01 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-28-2010, 07:36 AM
  5. How to format TextBox output as numerical
    By knarf in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-15-2009, 02:52 PM
  6. Numerical problem
    By aashrestha in forum Excel General
    Replies: 5
    Last Post: 05-16-2009, 05:30 AM
  7. Can I set the numerical type of Cell to Hex format
    By hon123456 in forum Excel General
    Replies: 2
    Last Post: 01-13-2005, 09:06 AM

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