Excel always convert date, time or currency format to a number format If want to add them with a Text. So If you want to merge Date, Time or Currency (any number format) with a text string within a Excel cell we can easily get this by using TEXT function and custom formats.
The Excel TEXT function allows converting a value to a specific format based on the given format codes. The function is useful to display text after inserting a given number formatting.
Here we describe inserting Text with Date but if you want to know how to insert Text with currency or date in cell same formula will be applied only it change date format to a currency format.
Query: We have three columns, Our task is to Insert Text “52 week low” share price with the respective date in a cell.
Formula to implement:
Download Your Example Excel File
Result: “52 week low (October 23,2018)”
If you want show month name in short form the same formula will be applied only in place of four “m” we used 3.
=”52 week low “&”(“&TEXT(C2,”mmmm dd,yyy”)&”)”
Result: 52 week low (Oct 23,2018)
Returns text and date with shorter month name.
It will give an error if you simply type text and link the cell.
=”52 week low ” & C2
52 week low 43761
The above formula return text with only a number but without date format.
like when you wish to convert date (23-10-2018) with day in cell D5 to text with day name.
=TEXT(C2,”ddd, mmm d, yyyy “)
Result: Tue, Oct 23, 2018
It will return date to text form along with day name.
Now how to insert Text with Currency, check the below example:
Result: Payment received $200
The formula display text with currency ($) sign
If you write formula this way it shows error
=”Payment received ” & C5
Result: “Payment received 200” ‘without Currency Format ($)