+ Reply to Thread
Results 1 to 12 of 12

Displaying and reading 27K as a number and not text

  1. 02-13-2015, 06:07 AM #1
    jm0825
    jm0825 is offline
    Registered User
    Join Date
    02-13-2015
    Location
    Waco Tx
    MS-Off Ver
    2010
    Posts
    2

    Displaying and reading 27K as a number and not text

    How do I display say 27000 as 27K? I played with formatting the number, but didn't have much luck.
    Adding K to the end of 27000 produced 27000K
    I am trying to use conditional formatting so I can color code values
    0-30K red, 31K to 60K yellow, 61K and up green
    So how do I retain the 27K as a number value instead of text.

    Thanks

    Jack
    Reply With QuoteRegister To Reply
  2. 02-13-2015, 06:09 AM #2
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Displaying and reading 27K as a number and not text

    Hi.

    http://stackoverflow.com/questions/2...nds-k-in-excel

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/
    Reply With QuoteRegister To Reply
  3. 02-13-2015, 06:10 AM #3
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Displaying and reading 27K as a number and not text

    Maybe like this

    b1=a1/1000&"K"
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.
    Reply With QuoteRegister To Reply
  4. 02-13-2015, 06:11 AM #4
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Displaying and reading 27K as a number and not text

    Quote Originally Posted by oeldere View Post
    Maybe like this

    b1=a1/1000&"K"
    That will render it as a text value. The OP wants it to remain numerical, yet display as such.

    Regards
    Reply With QuoteRegister To Reply
  5. 02-13-2015, 06:12 AM #5
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.96 for Mac MS 365
    Posts
    8,690

    Re: Displaying and reading 27K as a number and not text

    well if the values are all in the tens of thousands, such as 10000 to 99999 you could use a formula like =LEFT(a2,2)&"K" assuming your values begin in cell A2 (adjust as needed). BUT, for conditional formatting I don't believe excel will read that in the 0-30K and 31K to 60K etc way you want since that sounds like ranges and you'll be converting them to text which excel won't see as a number any more.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci
    Reply With QuoteRegister To Reply
  6. 02-13-2015, 06:15 AM #6
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Displaying and reading 27K as a number and not text

    Quote Originally Posted by Sambo kid View Post
    well if the values are all in the tens of thousands, such as 10000 to 99999 you could use a formula like =LEFT(a2,2)&"K" assuming your values begin in cell A2 (adjust as needed). BUT, for conditional formatting I don't believe excel will read that in the 0-30K and 31K to 60K etc way you want since that sounds like ranges and you'll be converting them to text which excel won't see as a number any more.
    Ditto my comment to oeldere:

    Quote Originally Posted by jm0825 View Post
    So how do I retain the 27K as a number value instead of text.
    Regards
    Reply With QuoteRegister To Reply
  7. 02-13-2015, 06:18 AM #7
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.96 for Mac MS 365
    Posts
    8,690

    Re: Displaying and reading 27K as a number and not text

    Ditto my comment to oeldere:
    you guys are just faster than me.
    Reply With QuoteRegister To Reply
  8. 02-13-2015, 09:42 AM #8
    jm0825
    jm0825 is offline
    Registered User
    Join Date
    02-13-2015
    Location
    Waco Tx
    MS-Off Ver
    2010
    Posts
    2

    Re: Displaying and reading 27K as a number and not text

    Thanks for the help it worked quite well.

    What do I look up and where do I find this information to be able to do this my self?

    I appreciate all the help!
    Reply With QuoteRegister To Reply
  9. 02-13-2015, 10:06 AM #9
    scottc_00
    scottc_00 is offline
    Forum Contributor
    Join Date
    01-08-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    100

    Re: Displaying and reading 27K as a number and not text

    Glad this is resolved but how about this as an alternative?
    If the number is always in the thousands you can set up a Custom Number Format like this 0#,K;[Red](0#,K)
    If the number may be some part of 1000 or you want decimal places you could use this 0.##0,K;[Red](0.##0,K)

    Good Luck
    Reply With QuoteRegister To Reply
  10. 02-13-2015, 10:10 AM #10
    scottc_00
    scottc_00 is offline
    Forum Contributor
    Join Date
    01-08-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    100

    Re: Displaying and reading 27K as a number and not text

    As for where to look to be able to do it yourself...You're there. I come her often and browse the posts for anything that is interesting and read the solutions. Also when I ask a question I ALWAYS do my part to investigate the answer until I FULLY understand what was provided to me. I am alway amazed at how much I can learn by disecting solutions I find here.

    Good Luck
    Reply With QuoteRegister To Reply
  11. 02-13-2015, 01:13 PM #11
    daveisalwayshere
    daveisalwayshere is offline
    Registered User
    Join Date
    02-05-2015
    Location
    Edmonton, Alberta
    MS-Off Ver
    2013
    Posts
    70

    Re: Displaying and reading 27K as a number and not text

    Why not have a hidden column with the numerical value and use that for all your formulas but have the visual cells looking like you want? that seems like the only solution that will work unless you want to change all your formulas to pull out the K and then multiply by 1000
    Reply With QuoteRegister To Reply
  12. 02-13-2015, 01:22 PM #12
    MrShorty
    MrShorty is offline
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,423

    Re: Displaying and reading 27K as a number and not text

    Quote Originally Posted by jm0825 View Post
    What do I look up and where do I find this information to be able to do this my self?
    When I was learning this stuff, I started with the help files in Excel. https://support.office.com/en-US/Art...rs=en-US&ad=US The section applicable to this specific format code is about half way done where it describes the use of commas as thousands separators or to scale a number by multiples of 1000.
    I will admit, that Microsoft's search engine generally did not like finding this particular page, but, once I became aware of it, I used it quite frequently until I became familiar with the different number format codes.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Reading excel files and displaying a column and file name using vba(macro) in excel
    By Premma in forum Excel Programming / VBA / Macros
    Replies: 26
    Last Post: 01-05-2014, 12:51 PM
  2. Reading Data which has been split as a NUMBER
    By cloudwalking in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-04-2013, 08:21 PM
  3. Reading and displaying numbers from one sheet to another
    By ramanan256 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-10-2011, 12:57 PM
  4. Reading the row number with VBA
    By johan@durban in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-11-2006, 01:20 AM
  5. [SOLVED] Pivot table: displaying text instead of number; can it
    By Allen in forum Excel General
    Replies: 2
    Last Post: 05-08-2006, 02:55 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1