Combining Numbers and Text in a Cell

Hi Allen,

You could also use a custom format similar to #.00 & " miles" which would obviate the need for the second column and, perhaps more importantly, allow for simple numeric entry or a formula without having to format text to look like a number. The user would need to ensure the column was made wide enough to display the likely maximum width of an answer.

Cheers,

Mitchell

P.S. One major difference between the two approaches becomes apparent when copying is used in conjunction with ' Paste Special '. Using my example below, copying each of the cells, and then pasting contents only into two different cells gives completely different results:

TEXT Function:
It's 14:05:00 on Wednesday, Dec 28, 2022

Custom formatting:
44923.5868039352

@Erik: thanks for mentioning the Ctrl-1 shortcut to open the cell format window.
Allen's approach will work the same way as yours in the '=NOW()' example. The following two approaches each yielded the same result in MS365 Excel:

Formula ' =NOW() ' in a cell, which was then custom formatted with
' "It's "hh:mm:ss" on "dddd, mmm dd, yyyy '

Formula ' ="It's "&TEXT(NOW(),"hh:mm:ss"" on ""dddd, mmm dd,yyyy") ' in a cell.

Notes
The single apostrophe & space combination is used to indicate what was entered in Excel and should not be entered as well, and
the inclusion of seconds in the time format allowed easy checking that the result shown in both cells is the same when the sheet is recalculated.

Which of the two methods (the TEXT() function or custom formatting) is best suited to particular circumstances depends on personal preference. Factors which may play a role and which occur to me follow. Comments with additional factors are welcome.

The TEXT() method results in a more unwieldy formula. In the example above this is not much of a problem, but if the formula to be evaluated to get the result is itself complicated, the resulting formula with formatting is even longer, which makes it more difficult to read and may mean that it becomes easier to make mistakes; in this case, custom formatting completely separates the function from the formatting, allowing the user to concentrate on one aspect at a time.

Copying the cell to a blank workbook: no problems encountered. The custom formatting is copied to the new workbook and is available for use there. BTW An export/import option in the Custom tab of the Format Cells dialogue would be a very handy addition to the currently available methods of setting up templates or simply copying cells and/or worksheets from one workbook to another.

Mohammed,
Add the 2 cells together, such as C2=A2+B2
then custom format the cell (Ctrl 1) Number - Custom & use

dd/mm/yyyy hh:mm:ss AM/PM as the format (see Figure 1 below)

HTH

Hi. I have some data in excel. Date is in separate cell: Eg; (16/06/2019) . Time is in separate cell. Eg; (9:11:45 AM)
Need to combine together in to once cell. But when converted it comes with numbers only.

Can you please fix this

In some cases, custom formatting might work better. For example, you can put "=NOW()" in a cell and it will remain a value. Then, highlight that cell, open the "Format Cells" window (ctrl-1 is one easy way), select the "Number" tab, and set the "Category:" to "Custom". Put this in the "Type:" box (including quotes):

"today is "dddd, mmm dd, yyyy

This method is works well for other instances, for example showing units. But is not ideal in cases where the text might frequently change, for example the 1+1 example.

To retain the cell as a numeric value and thence be able to affect the number of decimal places or use it as a number in a formula, etc, try custom formatting, ie "1+1=" # or some such. I use this a lot when I want to the number to be associated with a descriptive word like "Extra" or "Total = " or "Hold for tomorrow" or "George's share".