CODEWITHSUNDEEP
excelnumber-formatting
Dr. Manuel Kuehner
Dr. Manuel Kuehner

Reputation: 433

Excel: Formatting Number - Shorten the Number / Delete Digits

In Excel you can format numbers to meet specific need like currency or dates:

http://office.microsoft.com/en-us/excel-help/number-format-codes-HP005198679.aspx

I read the online help and other recources but I don't find the solution for my problem:

I have numbers that look like:

2014.13
2014.14
2014.15

or I could make them look like

201413
201414
201415

I am looking for a number format that removes the first 4 digits so that the result looks like

13
14
15

It this even possible with number format? I know how to do it with a normal formula but I need to use the number format.

enter image description here

Update

Thanks for the suggestions. I cannot work with cells and formulas because I use the numbers as the values for the x axes of a xy chart.

The numbers are year + kalenderweek. But I do not want to have so long numbers as tick labels in the chart I just want to show the kalenderweek. Excel offers the possibility to format the x axes values but I cannot apply normal formulas like right().

Upvotes: 1

Views: 4710

Answers (2)

Gary's Student
Gary's Student

Reputation: 96771

Select the cells you wish to format and run this small macro:

Sub HideCharacters()
    Dim D As String
    D = Chr(34)
    For Each r In Selection
        r.Value = Chr(39) & r.Value
        r.NumberFormat = ";;;" & D & Right(r.Value, 2) & D
    Next r
End Sub

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13517

For removing first four digit you can try somthing like this:-

=RIGHT(CELL_VALUE,2)

This will give you 2 digits from right thus removing 4 digits from left.

Upvotes: 1

Related Questions