In case you want to **convert** the current date to the **text** format, you can use the **Excel** **TEXT** function in combination with the TODAY function that returns the current date, for example: =TEXT(TODAY(), dd-mmm-yyyy How to convert Excel Time to text string. 12-30-2019 04:01 PM. For example, I have excel field showing 13:01 (0.542361111110949 in general format), I want input 0.542361111110949 to turn to output '13:01' text string (for data field [nvarchar] (160)) Dates and times in Excel are stored as serial numbers and converted to human readable values on the fly using number formats. When you enter a date in Excel, you can apply a number format to display that date as you like. In a similar way, the TEXT function allows you to convert a date or time into text in a preferred format. For example, if the date January 9, 2000 is entered in cell A1, you can use TEXT to convert this date into the following text strings as follows To change a time value like: 1:00:15 to text, you can use the 'TEXT' function. Example, if your time value (1:00:15) is contained in cell 'A1', you can convert it into a text by doing: Text(A1, h:mm:ss). The result still looks the same: 1:00:15. But notice that this time round, it has become a text value Example #1 - Convert Date to Text in Excel using TEXT Function From cell A2 to A10, we have the below values and convert them to the Date from B2 to B10. In order to convert them to date format, in cell B2 write the below formula. =TEXT (A3,dd-mm-yyyy

There are four simple ways to convert date to text in Excel: Using the Text Function (most commonly used) Using the Text to Column option Using the Copy-Paste method Using VB Use the TIMEVALUE function to convert text to time in Excel TIMEVALUE () Syntax. The TIMEVALUE function is used to convert a text value into a time value. The TEXTVALUE Function - worked example. To use this function, you need a cell that contains a text value which has been... Formatting the. The TIMEVALUE Function is categorized under Excel DATE/TIME functions. TIMEVALUE helps us convert a text representation of a time to MS Excel time.In MS Excel, time is stored internally as a numerical value. It doesn't recognize time in text form

The challenge is to use any tools in Excel to convert the time/duration stored a text into a numeric value that can be formatted as a time. The original data set contains a column of time/duration text that are in the following format. 1 hour 27 minutes 15 second Excel Formulas & Functions; How to convert DATE and TIME to text; Results 1 to 21 of 21 How to convert DATE and TIME to text. LinkBack. LinkBack URL; About LinkBacks; Thread Tools . Show Printable Version; Subscribe to this Thread Rate This Thread. Current Rating Excellent Good Average Bad Terrible 08-12-2005, 06:19 AM #1. ice_pack. View Profile View Forum Posts. Excel stores date with corresponding serial number. While calculating dates with numbers it gives a result, so we will convert Date into Text to avoid the above mistake. The TEXT function lets you change the way a number appears by applying to format to it with format codes The quickest way to convert an Excel number to text is to insert an apostrophe in front of the number (see rightabove). The apostrophe will not be displayed in the cell, but it will tell Excel to treat the contents of the cell as text. This is also a useful way to force Excel to display leading zeros on numbers such as telephone numbers

Value (required) is the value to convert to a text string. Format (required) is the formatting to apply when converting to a text string. =TEXT ( B2, dddd ) The above formula will convert our date value in cell B2 into the corresponding weekday name. In this example we get a value of Friday from the date 2020-09-18. Get the Weekday Number with the WEEKDAY Function. While the results aren't. The Excel TIMEVALUE function converts a time represented as text into a proper Excel time. For example, the formula =TIMEVALUE(9:00 AM) returns 0.375, the numeric representation of 9:00 AM in Excel's time system. Numeric time values are more.. Click the cell that contains the text-formatted date that you want to convert. Enter ) Press ENTER, and the DATEVALUE function returns the serial number of the date that is represented by the text date

TEXT function is language-specific. It requires the use of region-specific date and time format codes. Free Excel Course. Check out our Free Excel Crash Course and work your way toward becoming an expert financial analyst. Learn how to use Excel functions and create sophisticated financial analysis and financial modeling After installing Kutools for Excel, please do as this: 1. Select the cells with the text datetime you want to convert. And then click Kutools > Content > Convert to Date, see screenshot: 2. And then, you can see the text datetime cells have been converted to real datetime at once, see screenshots Excel Convert numbers to Text The Text function will accept a numeric value as the first argument, then based on the format code in the second argument to convert the number to text. You can convert all the standard number formats such as: dates, times, currency to Text string in excel. Convert Date to text with Text Function When you want. The DATEVALUE function in Excel converts a date in the text format to a serial number that Excel recognizes as a date. The syntax of Excel's DATEVALUE is very straightforward: =DATEVALUE(date_text Enter the formula in cell B2. =--TEXT (A2,00\:00\:00) Function will return the number. Format the cells in to hh:mm:ss. Copy the same formula in the range. This is the way we can convert the text into time by using the Text function

- Convert Time to Hours in Excel. The following spreadsheets show two different formulas that can be used to convert a time to a number of hours in Excel. Method 1: Simple Multiplication. The simplest formula to convert a time to a number of hours is shown below. In this case, the time is simply multiplied by 24 (the number of hours in one day)
- And, if that date is not in a proper format Excel will treat it as a text. Use this formula for this type to text date. = DATEVALUE ( MID (A6, FIND (A6)+6,2)&-& MID (A6, FIND (A6)+2,3)&-& RIGHT (A6,4)) 7. Date with a Day Name in the End. Here we have a date with the day name in the end
- Date and Time in Excel are stored as numbers. This enables a user to use these dates and time in calculations. For example, you can add a specific number of.
- Example 2: Text date Thursday.2015.January.25 1:30:20 in cell A1. Step 1: To first extract the year, the month and the day: Year: =MID(A1,FIND(.,A1)+1,4), which is 2015; Month: =MONTH(MID(A1, FIND(.,A1, FIND(.,A1)+1)+1, FIND(.,A1, FIND(.,A1,FIND(.,A1)+1)+1)- FIND(.,A1,FIND(.,A1)+1)-1)& 1), which is 0
- Set the Text property of the Label control to following: Text(DatePicker1.SelectedDate, [$-en-US]d. mmmm yyyy) If you want to convert the date format within the Date Picker control into d. mmmm yyyy, I have made a test on my side, the screenshot as below: Set the Format property of the Date Picker control to following: d. mmmm yyy
- Convert Dates to Text. Excel stores dates as serial numbers, where each whole number represents a unique date. This is extremely useful as it allows you to perform calculations on the dates, but in some cases you may wish to convert that date to text. Text Function. To convert dates to text, you can use the TEXT Function: =TEXT(B3,M/D/YYYY) The most important input in the TEXT Function is.

If the steps above didn't work, you can use this method, which can be used if you're trying to convert more than one column of text. Select a blank cell that doesn't have this problem, type the number 1 into it, and then press Enter. Press CTRL + C to copy the cell. Select the cells that have numbers stored as text ** Thanks, Leonid, but your formula still returns a text string, which cannot be used in a formula or PivotTable**. The aim of this tutorial was to convert the date text string to a number format in Excel. Perhaps you meant a formula more like this: =--TEXT(A2,dd/mm/yy) Where A2 contains a date text string 01-01-2014. Kind regards, Mynda In this video, we will demonstrate a variety of techniques to convert text to date values in Excel. These will include formulas and also good old classic Exc.. When we need to convert Date to Text in Excel Date To Text In Excel You can convert Date to Text in Excel through the most commonly used method, i.e., the text function or by using: Text-to-Column option, Copy Paste Method and VBA. read more, we need to use the TEXT function in excel. As I already mentioned, time & date in Excel is stored as numbers

How to Combine Date & Time in Excel. to merge/combine the date & time in excel cells, follow below steps, Step 1: Here is the simple formula to combine Date & Time in Excel. Step 2: A2 indices the first date in Date Column & B2 is for Time Column. Step 3: Type this formula = TEXT(A2,m/dd/yy )&TEXT(B2,hh:mm:ss) into next column Tip: What's also useful about the VALUE function is it will convert a cell containing date and time that is formatted as text, as you can see in row 7 above where 2014-1-1 8:00 became 41640.3333, which is the Excel serial number for the date and time: 1/01/2014 8:00 AM, whereas the DATEVALUE function, which we'll look at next, ignores the time portion of the text string End If. End If. Substitute_Hours = Replace (input_string, string_to_replace, * 1/24 +) End Function. You end up with a decimal number representing the time. For example 0.444213 representing string 10 hours 39 minutes 40 seconds.. The last step is to set the appropriate time format for the appropriate column * Hi There, I've got about 2700 rows of data*. One of the columns was reported in time (ex. 1 hour 6 minutes). I'm looking for a way to convert this General text into Hours (ie. 1.10 hours in the example above). Thank you in advance

Klicken Sie hier, um mehr über Time Convert zu erfahren. Konvertieren Sie die Zeit mit Superfunktionen in Dezimalstunden / Minuten / Sekunden . Tatsächlich können Sie in Kutools for Excel auch verwenden Kutools-Funktionen Zeit in Dezimalzahlen umwandeln. Nach der Installation Kutools für Excel, bitte gehen Sie wie folgt vor: （Kutools für Excel jetzt kostenlos herunterladen!) 1. Wählen. Convert Time to Seconds in Excel Formula #1, using multiplication formula. How to convert time to seconds in excel? to get the second number from a TIME value is to multiply it by the number 86400 (number of seconds in one day, 24 hours x 60 minutes x 60 seconds). The Formula =A3*86400 The Result. Formula #2, using CONVERT function. Fill the number argument with cell A3. Fill the from_unit.

Convert a Number to Text Using Excel Text to Columns. If you want to convert an entire column of Excel numbers to text, you might prefer to use the Excel Text to Columns command. However, this method might be less useful if your data spans several columns, as the Text to Columns command only works on one column at a time I am using Excel and trying to convert a formula answer into text value. Your help would be greatly appreciated Convert text timestamp into time in Excel Excel How Tos, Shortcuts, Tutorial, Tips and Tricks on Excel Office. We provide you with A - Z of Excel Functions and Formulas, solved examples for Beginners, Intermediate, Advanced and up to Expert Level Excel: Convert Text to Time. This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book. Problem: I have a spreadsheet where the times were imported as text. Strategy: Use TIMEVALUE to convert the text to time. However, the text dates have to be in the correct format. Excel for Developers https: Sign in to vote. I would like to convert todays date into text or a whole number representing the date. Example: 10/11/ into 10-11-10 or 101110. The purpose of this is to save a workbook as todays date with one click (name101110.xlsm). I have code that works to save but needs the user to provide the date. Is there a way to convert the date to a usable format.

If you want to convert the date to a month name, you can use the TEXT function with a specified format code like mmmin excel. You can try to run the following TEXT formula: =TEXT (B1,mmm) Assuming that you want to get the month name from the date value: 12/1/2018 in Cell B1, then you can enter the above TEXT function into Cell C1 MS Excel considers time to be a part of a day and it is stored as decimal fractions. Dates and times can be added and subtracted. Therefore they can be included in many calculations and VBA programs. That is you can compare two dates or subtract one date from another. You can learn more about this in our Excel course. There are two date systems in MS Excel- the 1900 and 1904. By default. This post will guide you how to convert time string to time in Excel. Assuming that you have a list of text string in your worksheet, and you wish to convert these time string to standard time format, how to do it. You need to use an formula based on the TIMEVALUE function, the LEFT function, the LEN function and the RIGHT function. Like below If you want to convert excel dates to text values (for your report or some other purpose), you can use the TEXT formula like this: =TEXT(A1,DD-MMM-YYYY) will convert date in Cell A1 to DD-MMM-YYYY format. You can pass any other date / time formats to TEXT formula as well. more: tutorials on TEXT() formula] How do you deal with troublesome dates? Of course, if it is a real date, we can always. The first thing we are going to do is separate the date and time information which is delimited by a comma. Highlight the column containing the data you wish to split. Select the Data tab and choose Text to Columns. The Convert Text to Columns Wizard - Step 1 of 3 window will appear: Make sure that the Original Data Type is set to Delimited

In excel, it is possible to convert text which is in an unrecognized format into a proper excel date. We can do this by parsing the text and assembling a proper date using a formula that is based on functions like; DATE, LEFT, MID and RIGHT. This article provides a conclusive procedure on how to convert text to date. Figure 1: Converting text to date in Excel. General syntax of the formula. In this lesson, I'll show you how to convert text to dates in Excel in a formula and non-formula way. When you import data to Excel, it may come in many different forms, with different delimiters. It's also important to be aware of dates notation used in a particular country. For example, people in the USA use different date format than the rest of the world. There are many ways to convert. Saving a text file as a XLS/XLSX spreadsheet will save you a lot of time and let you deal with your data more conveniently. But how can you import or convert those text files to Excel ones? This post is really helpful. Convert Txt to Excel. Part 1. How to Import Text to Excel; Part 2. How to Convert Text to Excel Online; Part 3. More Information about TXT(Text) And XLSX (Excel) Part 1. How to. How do I convert one time zone to another in Excel? Converting From GMT to Another Time Zone. Select the cell where you want the new time to appear, type =, followed by the cell containing the GMT and then add or subtract the time difference divided by 24. Press Enter. For U.S. time zones, use: =NOW()-5/24 for the Eastern time zone

3. Under column data format, select Text > Finish 4. Repeat steps 1-3 for range E10:E13 as well. As can be observed from the steps outlined above, this would be a time-consuming process because Text to column cannot be run on multiple columns as once Version 2: Convert von date and time andcomplex cells. With the following function you can safely convert all possible cells from Excel into the same display as text. This reads the text for each Excel cell, converts the format to text and then rewrote the text. sText = CStr(cell. text) 'cell. Value Time and dates are originally stored as serial numbers in Excel before they are transformed into values that can be read by us. How to use the Excel TEXT Function. We will now demonstrate how we can use the Excel TEXT Function to convert a date in number format into text. . Figure 2. Convert Date to Text in Excel To convert these strings to dates, we will use a two-step process. Step 1 involves using an Excel tool called Text to Columns, and Step 2 involves using the DATE and MONTH function. Step 1: Select the list of text strings you wish to convert to dates. Navigate to the Data Tools group under the Data tab and click on Text to Columns

If you have time stored as text, you can use the TIMEVALUE Function to return the time value. The TIMEVALUE function will convert the text to an Excel serial number from 0 which is 12:00:00 AM to 0.999988426 which is 11:59.59 PM. 1 = DATEVALUE (B3) TEXT to COLUMNS. Excel has the ability to convert data stored in a text string into separate columns. If there are values within this data, it will. Below are the steps to use Text to Columns to remove the time portion from the date: Select the cells from which you want to remove the time. Click the Data tab. In the Data Tools group, click on 'Text to Columns' option. In the Text to Column Wizard, do the following: Step 1 of 3: Select the 'Delimited' option The DATEVALUE function will convert text into a date value (probably saw that coming), while the VALUE function will convert text into a generic number value. The differences between them are minimal. In the image above, one of the values contains time information as well. And that will be a demonstration of the functions' minor differences

If you want to convert the numbers in the cell to Text without any formatting, then you can use the below formula. = TEXT (B2,0″) First you need to give the input number and apply the given formula. Enter a formula. It will convert the number to text with no special formatting. Refer to the below image. Example 1 Download Excel Start File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1076.xlsxDownload Excel File: http://people.highline.edu/mgirvin/ExcelIsF..

1) Convert Time Difference to Hours, Minutes and Seconds. You can calculate the time difference between two times by subtracting the end time minus the start time. If you get a number that looks completely out of place, what you need to do is to right click on that number and choose Format Cells. Go to Number - Custom. Choose the type [h]:mm:ss If you want to convert a number to time values, you need some additional operations, multiplying and dividing between days, hours, minutes, and seconds. Here, we're going to take a look at a simpler approach by using Excel's TEXT function. Syntax =TEXT(numeric value / 24, [h] hours, m minutes, s seconds) Steps. Begin by typing in =TEXT(Select or. DATES and TIME , if you are new to Excel and you have already tried using those format, I am pretty sure that you must have felt panicky at some sort of time. It is quite obvious because when we don't know the exact working of any system or process, we try to use it just by hit and trial method. It works sometimes and sometimes not. Here we are talking about Dates and Time in Excel. These. We do need some methods which we can use to convert a text to an actual date with a right format. So today, in this post, I'd like to share with you 10 methods which we can use to convert a text to date format in Excel. For converting a text to date we need to use a combination of different functions. So let's get started

An Excel formula and an example in code would be useful. As Peter mentioned, if you have a date as Excel understands it, just change the Format to General. If, on the other hand, it is a text value, then use the formula =--{cell with text date} to convert the text date+time. You will see a number. Format it as you require, e.g., mm/dd/yyyy hh:mm:s I have not found a way to **convert** these to numbers except for very **time**-consuming methods: use **text** **to** columns to get rid of the bp (which I can do on the initial parse when I still have spaces so that I can use Fixed Width parsing, but if I try to do it after the first parsing, then **Excel** has stripped the leading spaces and I have to use Delimited parsing and tell it to treat b as a. Description. The Microsoft Excel CONVERT function will convert a number from one measurement unit to another measurement unit. The CONVERT function is a built-in function in Excel that is categorized as an Engineering Function.It can be used as a worksheet function (WS) in Excel

Next, select the cells where you want to convert the text into numbers. Finally, right-click, type S and then S again to get to this Paste Special menu below. Once you're in this menu, choose Values in the top section, and Add in the bottom section, then click on OK. Doing this adds 0 to each cell in your range. VBA convert time string to time value. Thread starter roscoe; Start date Jul 22, 2010; R. roscoe Well-known Member. Joined Jun 4, 2002 Messages 1,026. Jul 22, 2010 #1 I have an array where I read in the contents of a file. One column of that array is a date (yyyy/mm/dd) and another is a time (hh:mm:ss). I need to take the first and last time and calculate the difference by subtracting. I'm. When that happens, Excel can treat those dates as text - in other words, they look like dates but don't behave like dates. For example you can't sort by date properly. This lesson looks at several ways you can convert a date which Excel is treating as text into a proper date value in Excel. Use the DATEVALUE function to convert text to a date Convert Text to Numbers - takes an extremely long time. unsolved . I have a massive data extract file that I am working with that is all text, I'm working in the 100,000's rows of data. For a particular column I need to convert it to number format, and when I highlight the column and click on the little exclamation point that appears in the upper left hand corner to convert the text to numbers. Convert text dates to actual dates. When dates are copied into Excel, they're often copied as text. Importing dates is a better option but, sometimes, even imported dates come in as text. When.

- Hi, I want to make a flow that when a text or csv file adds to SharePoint, convert it to excel or add the content to a Sharepoint list The text file rows are like: 02;E02;A-0192;10,000;10,000 so I need to set each part in a column. Would you help, please
- How to Convert Multiple Excel Sheets into CSV Files: Step 1 - Open VBA Window. First, make sure your Excel Spreadsheet is open and press ALT + F11 at the same time in order to open the Microsoft Visual Basic Application (VBA) window. If you cannot open the VBA window by pressing ALT + F11, there's an alternative way to do it
- Convert text to time 08-16-2019 06:12 AM. I have a table with a couple of text fields that employees put a time in such as 0800 or 1625. The system we input into doesn't allow it to be formatted as a time. I'm wondering the easiest way to convert it to a time value. I can't just add a column and concatenate with left two characters and right two characters with a colon in the middle as.
- About Press Copyright Contact us Creators Advertise Developers Terms Privacy Policy & Safety How YouTube works Test new features Press Copyright Contact us Creators.

Now you just have a text value to convert into the proper text case. 6. Get Formula's Result Value with Power Query . There could be a situation when you need to get the result value of a formula in a separate cell instead of simply converting them. And in that case, you can use a power query. You need to convert it into values before sending it to your customer. But the thing is you don't. Convert text to numbers using the VALUE function. EXCEL. = VALUE (B5) The formula uses the Excel VALUE function to convert the text numbers, captured in cell B5, to a numeric value. METHOD 2. Convert text to numbers adding 0. EXCEL. =B5+0. The formula adds 0 to the text number to convert it to a numeric value Using the Excel Convert Text to Columns Wizard. This Wizard allows you to separate data in a single column into multiple columns. Selecting the Text to Columns command will open the Wizard, which then walks you through the various steps. Let's do an example together. First, select the data range that you want to convert to columns and navigate to Data > Data Tools > Text to Columns. Once you. 1. Current time + TIME (hours difference from UTC,0,0), for ex.: A1+ TIME (3,0,0) 2. Current time + (hours difference from UTC/24) Add the above value to your date. For the above to work, Excel must recognize the entry as a date, not as text

Convert from excel time number to R POSIXct type. addCreator: Add another author to the meta data of the file. addFilter: Add column filters addStyle: Add a style to a set of cells addWorksheet: Add a worksheet to a workbook all.equal: Check equality of workbooks cloneWorksheet: Clone a worksheet to a workbook conditionalFormat: Add conditional formatting to cell The easiest way I know of (please let me know if you know a better way) to convert between US (mm/dd/yy) and European (dd/mm/yy) dates without using VBA in Excel is via Text to Columns. Let's look at an example: My system's regional settings are setup for US dates, therefore I need to convert th Convert Excel Data Types - from Text to Number/Date (General), vice versa Sometimes we receive data which has numbers or dates shown as Texts. You can tell if you see the numeric values left-aligned in cells. By default, all numeric values should align to the right. This often happens when the Excel data comes from an Access database or enterprise reporting systems. This can cause problem in. Hello Guys, I am having issue where the FM TEXT_CONVERT_XLS_TO_SAP, don´t upload time cells correct from EXCEL. The Excel Cell have for example: 19:48:17 After execute de FM TEXT_CONVERT_XLS_TO_SAP m

Click Add, then Convert. In the future if you have a similar fixed width text profile required, you can skip steps 3-9 and simply select the profile from the list provided. Furthermore, you can save the program configuration know as a Conversion Job from the file menu to recall at a later time. See Also: Conversion of Fixed Width Text File From Access I copy-paste, or copy paste-special-CSV into Excel, and then for each column must use Text to Columns to convert numerical values to numbers. Changing the format won't work, so I'm stuck doing Text to Columns one column at a time for 20-30 columns. This is going to be repeated for about 100 sample sites In the Ribbon, select the option 'Data' - 'Text to Columns'. For the first step, just press 'Next'. In step 2, uncheck every delimiter and press 'Next'. In step 3, make sure that the option 'General' is selected. Press 'Finish'. All values are now converted to numbers! Categories: Excel, Office. Tags: Excel, convert numbers to text to numbers

Your real problem is that you need to change the content of a cell, and are frustrated when changing the format doesn't accomplish it. Typically in such situations, you imported the data from another app, or initially formatted the cell as Text. E.. Dates in Excel are stored as serial numbers which can be switched from one format to another. There are many different ways to display a date. To convert a date into YYYYMMDD format, please copy the formula below and change A2 in your file. =Text(A2,YYYYMMDD) Alternatively, you can format the dates to the YYYYMMDD format with the steps below

First convert the number of seconds to number of days (by dividing by 60*60*24) and then add the result to the date 1/1/1970. The formula will look like. =CELL/ (60*60*24)+1/1/1970. The quotes around the date are required. If they are not present, Excel will treat 1/1/1970 as an expression Using the cell formatting function of Excel to format the cell as text has little or no effect. One way to correct the problem is to add an apostrophe before the numeric data in the cell. For example, if the cell contains the value 12345, change the value to '12345 which saves the numeric data as text. This can be a tedious process if there are. In excel everything is fine, but when we do convert text to number, then excel getting hang. Rawat. Regards. Inder Singh. Sunday, July 20, 2014 2:23 AM . text/html 7/20/2014 2:29:35 AM inder singh rawat 0. 0. Sign in to vote. If i do only few number it is happening without hang, if i do entire cell then excel getting hang. Hope, i have provided adequate information for your reference. Regards. Launch Excel 2010, open a datasheet in which you want to convert values to time format. We are intending to convert values in the Time (Values) field into hh:mm, now we will be adding another field with a label Time (hrs:mins). We will be writing a simple formula in the first row. Now lets add the formula; =B2/ (24 Well Excel can convert text months into their respective numbers. If you enter 1Sep in a cell Excel will convert that into the date 1/9/13 (Excel uses the current year). You can use that functionality to convert the text of a month into the month number. Assume A1 contains the word September. The following formula will convert that into the number 9. =MONTH(1&LEFT(A1,3)) Using the & symbol.

i need to convert excel text format to Date & Time format. 2013-JAN-01 04:14pm to 1-Jan-2013 04:14 pm Thanks Sujee. Reply Delete. Replies. Reply. Unknown February 1, 2013 at 9:32 PM. I need to convert 5 digit text, 10913 (= MDDYY) to a date: 1/09/13. Please help. Reply Delete. Replies. Reply . Rick Rothstein (MVP - Excel) February 8, 2013 at 1:49 AM @Sandy Winkelman, Give this formula a try. Microsoft Excel TEXT Function Tutorial. The TEXT Function lets you take a numeric value and convert its format using special symbols. It is like using the Number tab on the Format cells dialog box in Excel. It is at its best when taking a date serial number and returning the month, day and year name Convert a Range to Reference. Suppose you want to make a data validation that list values in A1:A3. First, name the range A1:A3 as data_list. Select Cell B1, click on Data > Data Validation > fill in data as below using Excel Indirect Function to convert text data_list to Reference