Home > Excel Help Portal > Using Text with Excel

<<  (Page 1)  Previous              Next  (Page 1)  >>

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.

51. Rearrange a Text File My friend came to me with a text file that he needed to extract 3 elements on suceeding lines and re-arrange them into a new order. Bringing the 4,800 row text file directly into Excel caused things to be split in an uncomfortable order and was going to take a lot of programming. Hope this... Keywords: Format, Arrays, Printing, VBA, Text Files, Parse

 
52. Write a Text File My friend came to me with a text file that he needed to extract 3 elements on suceeding lines and re-arrange them into a new order. Bringing the 4,800 row text file directly into Excel caused things to be split in an uncomfortable order and was going to take a lot of programming. Hope this... Keywords: Format, Arrays, Formulas, Links, Printing, VBA, Text Files, Parse

 
53. Parse Text File The data as it comes out of the accounting system looks very un-professional but improves when I place it in my template. CompArray = Array(5, 33, 10, 10, 10, 10, 10, 9) SplitArray = Array(6, 12, 45, 57, 68, 81, 112, 122) PasteArray = Array(1, 2, 3, 4, 5, 6, 9, 10) DetPlace = 0 Do While DetPlace =... Keywords: Arrays, Errors, Formulas, Links, VBA, Templates, Text Files, Parse

 
54. Extract Info from Text File Option Explicit Public xR, zZ As Long,... Keywords: Formulas, Links, VBA, File Name, Files, Text Files

 
55. Miscellaneous Code fragments of Worksheet functions Rows & Columns Most of the following example show obtaining the value located in Column D for the row specified in cell A1. Another example shows obtaining the column letter for a specified cell. =GetFormula() was used to show the formulas actually used. The MarkCells macro was used to place the... Keywords: Formulas, Macro, Web, Comments, INDIRECT, OFFSET, INDEX, CELL, MID, ADDRESS, VLOOKUP

 
56. How to Reverse the Order of Words in a Text String This article explains how to reverse the order of words in a comma-separated text string by using the FIND, LEFT, LEN, and RIGHT functions in... KB#75513. Keywords: Formulas, RIGHT

 
57. OverPunch Code -106.68 65.00 -215.55 -27006 -32490 =IF(B19 The second function is actually the first one I wrote. Later I wrote newPunch to use a self-contained... Keywords: SERIES, Arrays, Formulas, Defined Name, FIND

 
58. Creating proper names format There are two possibilities. =UPPER() will change the text to all upper case. =PROPER() will change the text to proper case.If cell A2 has the word "john smith", use these formulas:In B2: =UPPER(A2) to return JOHN SMITHIn C2: =PROPER(A2) to return John SmithCopy the formula from B2 down to all of... Keywords: Formulas, VBA, Web, UPPER, PROPER

 
59. Re-Arrange a Date Here is a quick and dirty to rearrange the order of elements in a date to display it in a totally different format. I have made it modular so you can change the parts around quickly to fit your... Keywords: Dates, Format, VBA

 
60. Custom Functions If Russell adds a new, temporary column to be used for sorting, he can use this new custom function IPForSort(). This code should be pasted into a code module in the... Keywords: Sorting, Format, VBA, Web

 
61. Splitting the contents of a cell/column into two distinct cells/columns There are a couple functions you will use to do this.=FIND("@",A2) will tell you which position contains the @ in a cell. In your example, this would return position 6.You can then use =LEFT(A2,FIND("@",A2)-1) to return emailand =MID(A2,FIND("@",A2),LEN(A2)+1-FIND("@",A2)) to return @domain.comP.S.... Keywords: VBA, Web, FIND, LEFT, MID

 
62. Splitting the contents of a cell/column into two distinct cells/columns There are a couple functions you will use to do this.=FIND("@",A2) will tell you which position contains the @ in a cell. In your example, this would return position 6.You can then use =LEFT(A2,FIND("@",A2)-1) to return emailand =MID(A2,FIND("@",A2),LEN(A2)+1-FIND("@",A2)) to return @domain.comP.S.... Keywords: VBA, Web, FIND, LEFT, MID

 
63. Removing a set number of characters from a cell What I need to do throughout a big worksheet was to edit the cell, move the cursor to the end of the cell and then delete the last 4 characters if the characters.This macro should do... Keywords: Macro, VBA, Web

 
64. Using Ubound For years, I did not use arrays because I did not understand them. Since so much of what I get called on to do is parsing text files, I place the parsing elements in arrays and only have one place to look for changes. ChgOarRay = Array(5, 13, 26, 41) ChgoCT = Array(7, 8, 13, 14) Line Input #1,... Keywords: Arrays, Formulas, Links, VBA

 
65. A Formula to Calculate a Ratio Excel provides no direct way to display the ratio between two values. For example, assume cell A1 contains 3, and cell B1 contains 24. The ratio between these two values is 1:8. Following is a formula, contributed by Douglas J. Roach, that displays the ratio between the values in cells A1 and B1:... Keywords: Add-In, Analysis ToolPak, Charts, Ratio, Format, Formulas, Printing, GCD

 
66. Write Contents of Multi Choice Box My example may be a little hard to follow because I had to parse the data into 2 fields as it was written to cells. For U = 0 To usrCreateNew.cmbTEaccts.ListCount - 1 'Items in Multi-select If usrCreateNew.cmbTEaccts.Selected(U) = True Then 'Items selected Cells(DaRow + G, 1) = _... Keywords: Formulas, Links, VBA, Parse

 
67. Move the Minus Sign to Front Keywords: Formulas, Links, VBA

 
68. Oracle Data to Excel - Custom Excel Solutions for Oracle from Mr Excel Excel is a registered trademark of the Microsoft Corporation.MrExcel is a registered trademark of the Tickling Keys, Inc. All contents Copyright 1998-2003 by MrExcel... Keywords: Export, SQL, Format, Files, Text Files

 
69. Find Number of a Letter Then all you need to do is fill down. Even if you sort, the find continues to work.... Keywords: Days, Arrays, Formulas, Links, VBA, FIND, LEFT

 
70. Add Tick Marks

 
71. Move Minus (Revisited) Keywords: Errors, Formulas, Links, VBA, Text Files, TRIM

 
72. Count Number by Month Remember To tell Excel that your formula is an array, hold the Ctrl & Shift keys while pressing Enter. Brackets {} will be displayed around it. {=SUM(IF(LEFT(TEXT(B3:B14,"mmddyy"),2)="12",1,0))} Starting from the middle, take the dates in B3 through B14 and convert to text in the format mmddyy.... Keywords: Dates, Format, Arrays, Formulas, Links, VBA

 

<< (Page 1)  Previous              Next  (Page 1) >>