Excel cell custom format

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!

Kota

Posted 2019-10-25T07:19:56.413

Reputation: 19

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 – 2019-10-25T07:37:11.940

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 – 2019-10-25T07:54:33.780

Answers

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

Razi

Posted 2019-10-25T07:19:56.413

Reputation: 1

This cannot help to solve OP's problem. Applying such formats will display the value rounded to 4. – Akina – 2019-10-25T07:38:05.603

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)

Jeremy Thompson

Posted 2019-10-25T07:19:56.413

Reputation: 1 122

Thanks, unfortunatelly I have no idea, how to use this. – Kota – 2019-10-25T09:23:20.397

Doesn't mean it's not the right answer. It would work as an answer... – spikey_richie – 2019-10-25T13:05:33.467

-1

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

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

spikey_richie

Posted 2019-10-25T07:19:56.413

Reputation: 2 497

This is not gonna help me, I need to change displayed value 3.6 to 35 555. – Kota – 2019-10-25T07:42:24.817

I could multiply the value by 10 000, but I would like to do that by cell formating. – Kota – 2019-10-25T07:43:10.793

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 – 2019-10-25T09:04:50.660

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 – 2019-10-25T09:24:13.370