+ Reply to Thread
Results 1 to 3 of 3

Display only 1 decimal place but when combined with text

  1. #1
    Registered User
    Join Date
    09-07-2010
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2007
    Posts
    41

    Display only 1 decimal place but when combined with text

    Hi,

    I have a value in a cell which varies such as:

    1.2345
    12.3456
    123.4567
    1234.5678

    The length of the number varies as above.

    I need to display this in another cell as:

    *1.2
    *12.3
    *123.4
    *1234.5

    I thought at first, simple I will just format the cell it references to a number and set it to 1 decimal place. But this doesn't work as although the cell it is referencing is displayed to one decimal place it actually reads the full value. So I thought I would set the cell which is to display the modified value to a number and set that to 1 decimal place. But that doesn't work when combining the value and the '*'. I assume because it no longer reads it as a number due to the addition of the '*' as such it displays the full number string to several decimal places.

    As the value can vary in length I can't see how I can use the Left or Right function such as:

    ="*"&(Left(E4,3) <--- This will work when it is say 1.2345 as it will display *1.2 but if the number were 12.3456 it would show *12. and miss off the decimal place.

    I have attached an example.

    Any help is most welcome
    Attached Files Attached Files
    Last edited by Leonthefixer; 06-23-2015 at 06:39 AM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Display only 1 decimal place but when combined with text

    Format cell as:

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

  3. #3
    Registered User
    Join Date
    09-07-2010
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2007
    Posts
    41

    Re: Display only 1 decimal place but when combined with text

    Thank you - so simple I am not sure why I didn't think about that having done similar in the past!

+ 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. Replies: 3
    Last Post: 09-29-2014, 07:21 AM
  2. Display Decimal value in text box ?
    By Breizh29 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-13-2010, 05:52 AM
  3. Format display decimal values but no decimal point
    By BecParko in forum Excel General
    Replies: 1
    Last Post: 10-18-2007, 04:42 AM
  4. How to display a balloon and place some text in it
    By avveerkar in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-19-2006, 04:59 PM
  5. Replies: 3
    Last Post: 03-18-2006, 02:25 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