1

I cannot find the way, how to create custom cell format in Excel.

For example, I have value 3.5555, which is displayed as 3.6. I would like to get displayed 35 555. If I insert number 5 ... I would like to see 50 000. If I enter number 5.1 .... I would like to see 51 000. I would like to basically display the values in 10 000.

Any ideas? Thanks!

  • Custom format can change decimal separator or remove/hide it, but cannot move it (the only exclusion is percent format which moves it 2 positions left - but the same with 4 positions move not exists). – Akina Oct 25 '19 at 7:37
  • Yes, I don't need to move the decimal separator. I think I need to set the conditions like "general" (to show the whole real value) + "conditon to make the value 3.5555 showed as 35 555" – Kota Oct 25 '19 at 7:54
2

Create a custom number format

On the Home tab, in the Number group, click More Number Formats at the bottom of the Number Format list Format Number box.

  • In the Format Cells dialog box, under Category, click Custom.

  • In the Type list, select the built-in format that most resembles the one that you want to create. For example, 0.00.

  • The number format that you select appears in the Type box.

  • In the Type box, modify the number format codes to create the exact format that you want. For example, 000-000-0000.

  • Your changes will not alter the built-in format. Instead, your changes create a new custom number format.

  • When you have finished, click OK.

Apply a custom number format

  • Select the cell or range of cells that you want to format.

  • On the Home tab, in the Number group, click More Number Formats at the bottom of the Number Format list Format Number box.

  • In the Format Cells dialog box, under Category, click Custom.

  • At the bottom of the Type list, select the built-in format that you just created. For example, 000-000-0000.

  • The number format that you select appears in the Type box.

  • Click OK.

Create and apply a custom number format

| improve this answer | |
  • This cannot help to solve OP's problem. Applying such formats will display the value rounded to 4. – Akina Oct 25 '19 at 7:38
1

Write a macro to split the decimal to a string, eg VBA code:

Sub Macro1()

    Range("J4").Select

    Dim cellA As Double
    cellA = Range("K3").Value2
    arr = Split(cellA, ".")

    Dim cellb As String
    cellb = arr(0) & " " & arr(1)

    Range("K4").Value2 = cellb
End Sub

VB Editor (Alt + F11)

enter image description here

| improve this answer | |
  • Thanks, unfortunatelly I have no idea, how to use this. – Kota Oct 25 '19 at 9:23
  • Doesn't mean it's not the right answer. It would work as an answer... – spikey_richie Oct 25 '19 at 13:05
-1

You need to increase the display precision. You can do this using the 2 icons highlighted in the image below

enter image description here

Highlight the cell(s) you wish to show to a higher/lower precision, then use the buttons to change the precision.

| improve this answer | |
  • This is not gonna help me, I need to change displayed value 3.6 to 35 555. – Kota Oct 25 '19 at 7:42
  • I could multiply the value by 10 000, but I would like to do that by cell formating. – Kota Oct 25 '19 at 7:43
  • You can't do math functions via cell formatting, you'll need to do =sum(value*10000) where value is your number. Your question wasn't clear, hence my answer... the downvote isn't helpful, given the lack of clarity in your question. – spikey_richie Oct 25 '19 at 9:04
  • Ah, thank you, I thought that my question was clear. Anyways, thanks for help. I can try to explain better. If I insert number 5 ... I would like to see 50 000. If I enter number 5.1 .... I would like to see 51 000. – Kota Oct 25 '19 at 9:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.