Home > Excel Help Portal > Using Text with Excel

<<  (Page 2)  Previous              Next  (Page 2)  >>

Using Text with Excel

The ExcelUser portal offers links to help pages created by top
Excel web sites. This section discusses using text in Microsoft
Excel.

1. Rearranging Data in Columns This page contains some VBA macros. If you need assistance to install or to use a macro please refer to Getting Started with Macros. For more depth see Install a Macro or User Defined Function on my Formula page. Speed and efficiency considerations can be seen in Proper, and other Text changes and... Keywords: Add-In, SERIES, Sorting, Dates, Colors, Fonts, Format, Styles, Arrays, Errors, Formulas, Defined Name, PivotTables, Printing, VBA, Macro, UDF, User Defined Function, Toolbar, HTML, Hyperlinks, Web, Comments, TRIM, REPT, TEXT, LEFT, TRANSPOSE, MID, RIGHT, LEN, CODE, CLEAN, CHAR, REPLACE, ADDRESS, CELL, ROW, INDIRECT, FIND, LOWER, UPPER

 
2. Insert Rows using a Macro I wanted to be able to insert a row and maintain the formulas but not to copy data that would be unique for the line. The propagation of data would be misleading so I want it to be blank so I have to enter it myself. See also use of OFFSET in formulas which simplifies the insertion, and deletion of... Keywords: Criteria, Sorting, Dates, Days, Colors, Format, Errors, Formulas, Printing, VBA, Macro, Files, Toolbar, Web, Comments, OFFSET, ROW, INDIRECT, VLOOKUP, SIGN, MID, INDEX, MAX

 
3. Worksheets in VBA Coding and in Worksheet Formulas Keywords: Sorting, API, Colors, Fonts, Format, Arrays, Errors, Formulas, Links, Startup, Clipboard, VBA, Macro, User Defined Function, Setup, Files, Templates, Toolbar, HTML, Hyperlinks, Web, Comments, Window, CELL, INDIRECT, MID, LARGE

 
4. SORTING, some notes on sorting in Excel Material on backups has been moved to it's own page. This page contains or refers to some text pages containing VBA macros and User Defined Functions. If you need assistance to install or to use a macro or function please refer to my «Getting Started with Macros« or delve into it deeper on my... Keywords: SERIES, Criteria, Sorting, Days, Download, Borders, Format, Outline, Arrays, Errors, Formulas, Links, VBA, Macro, VBE, Toolbar, Web, Comments, CHAR, LEN, TEXT, LEFT, RIGHT, REPLACE, REPT, VLOOKUP, MATCH, CHOOSE, TRIM

 
5. Strings and Manipulations Text Functions, Workbook Before continuing please be aware of the following information available in Excel HELP WorksheetDescription VBA CHAR Listed by Tom Ogilvy from HELP (VBA HELP), but I can't find which help. Action Keywords Compare two strings. StrComp Convert strings. StrConv Convert to... Keywords: Sorting, Wildcard, Download, Fonts, Format, Arrays, Errors, Formulas, Links, VBA, Macro, UDF, User Defined Function, Error Message, HTML, Hyperlinks, Web, Comments, CLEAN, ASC, CONCATENATE, FIND, LEFT, LEN, MID, PROPER, REPT, RIGHT, MAX, EXACT, SUBSTITUTE, CODE, LOWER, UPPER, COUNTIF, TRIM, CALL, CHAR, ROMAN, REPLACE

 
6. Pathname in headings, footers, and cells This page contains some VBA macros. If you need assistance to install a or use a macro please refer to instructions on my my Getting Started with Macros page, or for more complicated situtations to my Formula page. Coding Example using a VBA macro: Leo Heuser =fname("filename") The following is... Keywords: Sorting, Dates, Statistics, Colors, Fonts, Format, Styles, Errors, Formulas, Startup, Page Setup, Printing, VBA, Macro, Object, VBE, File Name, Templates, Toolbar, Web, Comments, Window, CELL, LEFT, INFO, SUBSTITUTE, MID, RIGHT, NOW, COLUMN

 
7. VLOOKUP Worksheet Function This is the simplest example that I can come up with. Note the use of TRUE in the formulas indicating that the value found in the table does not have to be an exact match but must be less than or equal to the lookup_value used. For VLOOKUP the first column of the range is the used to match the... Keywords: Criteria, Format, Arrays, Errors, Formulas, Defined Name, VBA, Macro, Hyperlinks, Web, Comments, VLOOKUP, INDEX, MATCH, MAX, OFFSET, CHOOSE

 
8. XL2GIF and Manipulations The XL2GIF macro on this page was graciously supplied by Harald Staff I thought I might add a little item to your "Save as HTML" stuff. This is a little Excel8/9 routine that prompts for a worksheet range selection and saves this as a GIF image. We use an extended version of this for web-publishing... Keywords: Add-In, Charts, Download, Export, OLE, Colors, Gridlines, Errors, Formulas, Links, Defined Name, Clipboard, VBA, Macro, Files, Toolbar, HTML, Web, Comments, Window

 
9. Extraction of a Group of Digits and Dashes, from postings by Harlan Grove Keywords: Sorting, Patterns, Arrays, Errors, Class, VBA, Macro, Object, UDF, Web, Comments, FIND, LEFT, COUNTIF

 
10. Number Conversions This page contains some VBA macros. Assistance to Install a Macro or User Defined Function on my Formula page. Conversion of numbers which appear correct but are not validly interpreted in Excel. Numbers with a right minus appear correct but are treated as Text. Numbers in US use period as decimal... Keywords: Query, Sorting, Dates, Colors, Format, Errors, Formulas, VBA, Macro, User Defined Function, Toolbar, Web, Comments, TEXT, LEN, CHAR, TRIM

 
11. How to convert Text to Numbers in Excel 2003 This step-by-step article describes how to convert cells that contain text to cells that contain numbers. When you import a file that was created in another program (such as dBASE or Lotus 1-2-3) or that was downloaded from a mainframe, Excel 2003... KB#822665. Keywords: Third-Party, Format, Errors, Formulas, Macro, VBA, Web, AVERAGE, LEFT, TRIM, CLEAN, VALUE

 
12. Quicken Utilties for Excel Option Explicit Dim Flag, Flag2 As Boolean 'Flags to keep the Find Procedures running Dim HeaderRow, TotalRow As Integer 'Number of the Header and Total Rows Dim Counter As Integer 'Counter to ensure searches are relative to activecell Dim TempString, TempTest As String 'Strings that are used as... Keywords: Add-In, Sorting, Months, Format, Outline, Errors, Printing, Clipboard, VBA, Macro, Web

 
13. How to convert Text to Numbers in Microsoft Excel 2002 This step-by-step article describes how to convert cells that contain text to cells that contain numbers. When you import a file that was created in another program (such as dBASE or Lotus 1-2-3) or was downloaded from a mainframe, Microsoft Excel... KB#291047. Keywords: Third-Party, Format, Errors, VBA, Macro, Web, AVERAGE, TRIM, CLEAN, VALUE

 
14. SHELL invoked from VBA This should work, it will output one line per selected cell cells are numbered left to right and down, so selecting a single column should work. In Excel 2000 you can have multiple selection ranges if you have multiple selection ranges each range will be processed independently in the order that it... Keywords: Export, Startup, Printing, VBA, Object, File Name, Files, Text Files, HTML, Hyperlinks, Web, Comments

 
15. Validate / Validation Keywords: Format, Errors, Formulas, Sounds, Defined Name, VBA, VBE, Web, Comments, Validation, LEN, ROUND, VLOOKUP, COUNTIF

 
16. How to Convert Text to Numbers When you import a file that was created in another program (such as dBASE or Lotus 1-2-3) or that was downloaded from a mainframe, Microsoft Excel may recognize some numbers as text. This causes functions such as SUM and AVERAGE to ignore the... KB#181298. Keywords: Format, VBA, Macro, Web, AVERAGE, TRIM, CLEAN, VALUE

 
17. How to Convert a Numeric Value into English Words This article contains sample Microsoft Visual Basic for Applications functions that you can use to convert a numeric value into its equivalent in English words. For example, you can change 32.50 into Thirty Two Dollars and Fifty Cents by... KB#140704. Keywords: Formulas, VBA, Macro, Web

 
18. How to Convert a Numeric Value into English Words This article contains sample Microsoft Visual Basic for Applications functions that you can use to convert a numeric value in a Microsoft Excel worksheet cell into its equivalent in English... KB#213360. Keywords: Formulas, VBA, Toolbar, Web

 
19. How to Force Macro Code to Wait for Outside Procedure In the versions of Microsoft Excel listed at the beginning of this article, you can use a Visual Basic for Applications macro to run other Windows and MS-DOS applications and procedures. The macro code in Microsoft Excel continues to execute... KB#147392. Keywords: Errors, VBA, Macro, Files, Text Files, Web

 
20. Function to Convert Degrees/Minutes/Seconds Angles to or from Decimal Angles Angular measurements are commonly expressed in units of degrees, minutes, and seconds (DMS). One degree equals 60 minutes, and one minute equals 60 seconds. To simplify some mathematical calculations you may want to express angular measurements... KB#213449. Keywords: Format, Formulas, VBA, Web

 
21. Excel - a VBA function to extract text If you import a text file of data into an Excel worksheet you may wish to extract a particular range of characters from each record. For example if each data record (or row) looks something like 'A100 John 23.5 South' you may wish to extract the number (23.5) which is the third out of four items in... Keywords: SERIES, Format, Formulas, VBA, Macro, User Defined Function, Text Files, Parse, MID, FIND, VALUE

 
22. How to Force Macro Code to Wait for Outside Procedure In Microsoft Excel, you can use a Visual Basic for Applications macro to run other Windows and MS-DOS applications and procedures. The macro code in Microsoft Excel continues to execute even after the external procedure has been initiated. You... KB#214248. Keywords: Errors, VBA, Macro, Files, Text Files, Web

 
23. Sorting TCP/IP Addresses This page contains or refers to some text pages containing VBA macros and User Defined Functions. If you need assistance to install or to use a macro or function please refer to my «Getting Started with Macros« or delve into it deeper on my Install page. Frequently you have a column of codes that... Keywords: Sorting, Outline, Arrays, Formulas, VBA, Macro, Toolbar, Web, Comments, TEXT, LEFT

 
24. How to calculate Ages Before 1/1/1900 in Excel 2000 Although Excel date formulas can only use dates entered between 1/1/1900 and 12/31/9999, you can use a custom Microsoft Visual Basic for Applications function to calculate the age (in years) of someone or something that was first created... KB#245104. Keywords: Dates, Years, Formulas, Macro, VBA, Web, Parse

 
25. OnEntry macro to create a running total in a cell comment in Excel In Microsoft Excel you can avoid circular references when you create a running total by storing the result in a non-calculating part of a worksheet. This article contains a sample Microsoft Visual Basic for Applications procedure that does this... KB#213445. Keywords: Errors, VBA, Macro, Circular References, Web, Cell Note

 
26. Convert Numbers to Words In all fairness, I stole this from Microsoft. I get a request for this virtually every week. Or get a text file with all the custom functions ready to paste right in. With the custom functions, SpellNumber, GetHundreds, GetTens, and GetDigit, you can convert a numeric value into its equivalent in... Keywords: Formulas, Links, VBA, Macro, Text Files

 
27. Automate a complex formula with a UDF Jean asked this week's question. I am trying to convert a supplier price book to match their UPC codes to our UPC code system. Their price book has a column with a series of numbers eg. 000004560007 OR cel000612004. What I haveto do is two fold. I need to take the first three zeroes out without... Keywords: SERIES, Formulas, Sounds, VBA, Macro, Web, REPLACE, FIND

 
28. Converting Feet and Inches to decimal feet I have a column of lengths in the format of 12' 6 7/8". How can I convert this to decimal feet? I don't want to split the column into two parts. Then, how can I convert back to feet and inches?. The... Keywords: Format, Errors, VBA, Web

 
29. Macro to Obtain a List of Paper Names Supported by the Active Printer There is no built-in feature in Microsoft Visual Basic for Applications to obtain a list of paper names (Letter, Legal, A4, and so forth) supported by the active printer. This article contains Visual Basic for Applications sample code that returns... KB#229718. Keywords: API, VBA, Macro, Web

 
30. Macro to Obtain a List of Paper Names Supported by the Active Printer There is no built-in feature in Microsoft Visual Basic for Applications to obtain a list of paper names (Letter, Legal, A4, and so forth) supported by the active printer. This article contains Visual Basic for Applications sample code that returns... KB#244639. Keywords: API, VBA, Macro, Web

 
31. How to Use the Left, Right, Mid, and Len Functions in Visual Basic This article contains examples of how to manipulate text strings using the Left , Right , Mid , and Len functions in Microsoft Visual Basic for Applications in Microsoft... KB#213646. Keywords: VBA, Macro, Web

 
32. Use event handlers to simplify time input This week's question comes from John stationed in Okinawa. I am building a spreadsheet to reflect departures and arrivals. There will be basically three cells: Actual Time of Departure, EstimatedTime Enroute, and Estimated Time of Arrival. I would like for the user to just be able to enter (for... Keywords: Format, Formulas, VBA, Macro, Web, Window

 
33. Convert Numbers into Words In all fairness, I stole this from Microsoft. I get a request for this virtually every week. Or get a text file with all the custom functions ready to paste right in. With the custom functions, SpellNumber, GetHundreds, GetTens, and GetDigit, you can convert a numeric value into its equivalent in... Keywords: Formulas, VBA, Macro, Text Files

 
34. How to Convert All Files on a Server to Microsoft Excel The Microsoft Excel File Conversion add-in converts all of the files in one folder. This article describes how to use a Visual Basic for Applications macro to convert the files in a folder and all of its... KB#213699. Keywords: Add-In, Format, VBA, Macro, Object, Files, Web

 
35. Sample Code to Change Source of Linked Excel Worksheet This article provides a sample Microsoft Visual Basic for Applications macro (Sub procedure) that searches a presentation for linked Excel OLE objects and changes the linked source of these objects to a single source workbook that you specify.... KB#222708. Keywords: Charts, OLE, Links, VBA, Macro, Object, Web

 
36. Sample Macro Code to Change Case of Text Although Microsoft Excel has functions that change the case of text, there are times when you may want to change text case programmatically. This article contains three sample Microsoft Visual Basic for Applications macros ( Sub procedures) that... KB#291323. Keywords: VBA, Macro, Web, UPPER, LOWER, PROPER

 
37. Sample Macro Code to Change Case of Text Although Microsoft Excel has functions that change the case of text, there are times when you may want to change text case programmatically. This article contains three sample Microsoft Visual Basic for Applications macros ( Sub procedures) that... KB#213649. Keywords: VBA, Macro, Web, UPPER, LOWER, PROPER

 
38. Creating a Megaformula This tip describes how to create what I call a "megaformula" -- a single formula that does the work of several intermediate formulas. An Example The goal is to create a formula that returns the string of characters following the final occurrence of a specified character. For example, consider the... Keywords: Charts, Format, Formulas, Printing, SUBSTITUTE, FIND, RIGHT

 
39. Visual Basic Function to Format Complex Numbers Because Microsoft Excel stores complex numbers as text, complex numbers cannot be formatted like real numbers. This article provides sample code for a user-defined function that applies number formats to complex... KB#213294. Keywords: Format, VBA, Web

 
40. How to Return the Nth Word from a String This article contains a sample Microsoft Visual Basic for Applications function that extracts a particular word from a... KB#152568. Keywords: Formulas, VBA, Toolbar, Web

 
41. Excel Code Examples - Personalized Greeting I had gotten in the habit of sending my friends an email with the words “Happy Birthday” dancing down the page. Each time I copied from the original, I would add a new twist. But alas, something was missing. It was too much trouble to personalize it. I could not add a friends name without resorting... Keywords: SERIES, Printing, VBA, Macro, Text Files, Links

 
42. How to Programmatically Find the Nth Word in a Text String In Microsoft Excel, you can create a Microsoft Visual Basic for Applications function that can extract a specific word from a text string. This article describes how to create and use a custom function to find a specific word in a text... KB#213268. Keywords: Formulas, VBA, Web

 
43. Excel Filters -- AutoFilter Tips AutoFilter Tips Some tips and techniques for working with AutoFilters, and some workarounds for problems you may encounter. Limits to Dropdown Lists Count of Filtered Records in Status Bar Filter for Text in a Long String AutoFilter Basics Limits to Dropdown Lists An AutoFilter dropdown list will... Keywords: AutoFilter, Criteria, Formulas, LEFT, SUBTOTAL, COUNT, COUNTA

 
44. Build A Formula Most of the salesmen’s names were in the system like AB, RD, TS, etc. Occasionally 2 salesmen would split a commission and I had to work out a scheme that my program could always recognize.The syntax is 2 initials, the first commission percentage, 2 more initials and the second percentage; i.e.... Keywords: Sales, Errors, Formulas, Links, VBA

 
45. WordCount Keywords: VBA, Object, Web, Comments, TRIM

 
46. More Select Case Sometimes it is easier to look for a common condition rather that write multiple IF statements. These examples illustrate different kinds of conditions for “Select Case”. This first example is in the middle of some code that is called from one of two different macros; whichever one sends its own... Keywords: Years, Colors, Format, Errors, VBA, Macro

 
47. CRLF - Lines Split by Carriage Return, Line Feed Keywords: VBA, Macro, HTML, Web, Comments, CODE

 
48. How to bypass the Text Import Wizard When You Import Files in Excel 2000 This step-by-step article describes how to bypass the Text Import Wizard when you import files in Microsoft Excel 2000. When you import text files into Excel 2000, the Text Import Wizard may automatically appear and display the first line as... KB#214295. Keywords: File Name, Files, Text Files, Parse

 
49. How to Bypass the Text Import Wizard When You Import Files When you import text files into Microsoft Excel, the Text Import Wizard may automatically appear and display the first line as shown using the default parse line. You can bypass the Text Import Wizard and accept the defaults by holding down the... KB#110760. Keywords: File Name, Files, Text Files, Parse

 
50. How to Display the File Name of a Worksheet Without Using a Macro This article describes how to create a formula that displays the file name of a worksheet in a cell. For example, for a file with the path C:\Excel\Data\Test.xls the formula returns... KB#214084. Keywords: Arrays, Errors, Formulas, File Name, RIGHT, MAX, ROW, MID

 

<< (Page 2)  Previous              Next  (Page 2) >>