Home > Excel Help Portal > Using Text with Excel

# Using Text with Excel

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

1. 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

2.
3. 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

4.
5. 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

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

8.
9. 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

10.
11. 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

12.
13. 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

14.
15. 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

16.
17. 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

18.
19. 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

20.
21. 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

22.
23. 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

24.
25. 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

26.
27. 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

28.
29. 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

30.
31. 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

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

34.
35. 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

36.
37. 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

38.

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

42.
43. 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

44.
 Excel User's Home ExcelUser Blog Site Map Contact Excel for Business Excel Dashboards Excel Solutions Exploring Excel BI for Excel Business Tools Excel Catalog Affiliate Program Excel Help Portal

ExcelUser, Inc.
http://www.ExcelUser.com