Excel Formatting Question: Custom formatting for Text

[Markus64]

I'd like to use the cell formatting to show only the first letter of the entry of a cell.
I know it would be possible to use the left function and upper function to have a capital letter.
What I'd like to do is to have a dropdown list:

• Education
• Office
• Private Business
• Sick
• Travel
• Vacation

In my cell I'd like to have the text "Vacation" but showing "V" only.

I tried in format cells/custom. Unfortunately I wasn't successful.

I didn't find any hints or documentations.

I'm happy to hear if there is a solution to solve this.

[#2]

Hello, you can use flash fill. Assuming you have
Education
Office
Private Business
Sick
Travel
Vacation


... All in cell A1 and other cells below. In column B1, type in B click Enter and press CTRL + E.

To create list drop down, click on Data Validation under the Data tab and select List from the dropdown. Select all the data yt want and click OK

[#3]

Hi Abiola,

many thanks for the prompt answer.
Flash fill will change the entry in the cells.
I'd like to have the entries correctly but only shown the first letter.
e.g. As for a date you could show only the day, even the whole date is in the cell.
Regards

Markus

[#4]

@Markus64 What an interesting question. I suspect this is not possible and it can't be compared with date formatting. Since dates have a given structure (year, month, day) one can format a cell to display only part of the entire structure. A text, on the other hand, is just a string of characters. But I can't help wondering why you would want to type the whole word and only display the first letter. Can you elaborate a little? Perhaps there is another way to achieve what you want.

[#5]

@Riny_van_Eekelen 

Hi Riny,

many thanks for your answer.
I would have the whole word in the drop down list (e.g. Vacation). If you select it will be filled in the cell.

If you have a calendar overview for many people for a whole month or more, it's quite easier to have only the first letter shown in the cell.
The other way round you would have the first letters only in the drop down list.
At the end it's a question of user-friendliness and better overview. :)

However, it was a nice try to challenge Excel functionality.
The workaround will be a duplicate table where I will use the LEFT function.
If you have a better idea, let me know.
Regards
Markus

[#6]

@Markus64 I see! Consider including a legend in your calendar overview so that the user knows that V stands for Vacation, S for Sick etc.

This can be one row across the top or bottom of the overview. Just an idea but perhaps not a solution you can live with. Up to you!

[#7]

Hello @Markus64 

I also requested this functionality and I have found a solution using conditional format. Check https://www.extendoffice.com/documents/excel/4264-excel-display-different-text-than-value.html

Zdenek Moravec