Screenupdating false word
Sub Fix USzip5() 'David Mc Ritchie 2000-04-28 notposted, updated 2001-12-14 ' USzip5 Application. Calculation = xl Manual Dim cell As Range Dim c Value Dim c Pos As Long Selection. Special Cells(xl Cell Type Constants, 1) 'special modification to also use 3 digits as if valid If Len(cell) = 4 Or Len(cell) = 5 Or Len(cell) = 3 Then c Value = cell. Sub Fix_Phone Area Code() Dim cell As Range On Error Resume Next 'in case nothing found Dim Area Code As String Area Code = "412" Area Code = Input Box("Supply your local area code " & _ "to be prefixed to phone numbers without an area code" _ & Chr(10) & " i.e. Because Rows are copy and pasted and because TRANSPOSE is used in this macro all formatting, and formulas are preserved. Part 1, Rotate the Rows Part 2, TRANSPOSE the rotated Rows for selection area -- Full rows Note: As written the selection area must include cell A1, and the original selection area is really the entire rows. Address Local(0, 0) & _ Chr(10) & "Press OK to process those than can be split", _ vb OKCancel) If i Answer = vb OK Then Go To Do Any Way Go To terminated End If Next ir Do Any Way: For ir = 1 To i Rows If Len(Trim(Selection. Offset(0, 1))) Considerations in working with data from HTML sources The macro above does not include replacing a non breaking space character ( ) typically used in HTML, with a normal space.
Replace What:=Chr(160), Replacement:=Chr(32), _ Look At:=xl Part, Search Order:=xl By Rows, Match Case:=False 'Trim in Excel removes extra internal spaces, VBA does not On Error Resume Next For Each cell In Selection. " & Area Code _ & " will prefix current entries with ""(" & _ Area Code & ") """, "Supply Area Code", Area Code) If Area Code = "" Then Exit Sub Area Code = "(" & Area Code & ") " For Each cell In Intersect(Selection, _ Selection. This is practical as long as there are not more than 256 rows selected because of the longstanding 256 column limitation in Excel. Insert Shift:=xl Up Next i 'Have flipped the rows, next step is to TRANSPOSE data with copy abc = "1:" & n Rows - 1 Range(abc). Do you want to continue " & _ "with a TRANSPOSE using COPY? Paste Special Paste:=xl All, Operation:=xl None, Skip Blanks:=False _ , Transpose:=True Range(Cells(1, 1), Cells(n Rows - 1, 256)). Calculation = xl Calculation Automatic 'pre XL97 xl Automatic Application. Sep Term() Can be used to separate the street number from the rest of the street name. You can achieve the replacement with Worksheet Ctrl H, Replace: Alt 0160, With: (space) with macro code. Replace What:=CHR(160), Replacement:=CHR(32), Look At:=xl Part, _ Search Order:=xl By Rows, Match Case:=False A worksheet solution for the above.
Special Cells(xl Constants, 2) 'trim text cells cell. Special Cells(xl Constants, xl Text Values)) If Len(cell) = 8 Then 'as in 555-1212 cell. Value) End If Next cell 'On Error Resume Next -- continues in effect For Each cell In Intersect(Selection, _ Selection. If you selected an entire row for instance your data would be so far to the right that it would take you awhile to find it. This macro was written to normalize a spreadsheet that could best be views sideways and had rotated cells that became available in XL97. ", vb OKCancel, "Question") If i 1 Then Go To done Selection. Can also be used to separate a term from a definition. The next column will be tested that it contains a blank. This will leave the original column and the two new columns wns will be dependent on the original.
Special Cells(xl Constants, xl Numbers)) If cell Revers I() can be used to reverse the order of items in a row, column, or range. Applied to a SINGLE ROW, the macro will flip about a vertical axis, or a horizontal axis for a column; otherwise, it really isn't a flip. If you select a range of columns and rows the item in the upper left will reappear in the lower right corner. Until rewritten arrangement or presence of cells not in selection is undefined. Count If n Rows 256 Then Go To done n Rows = Input Box("Specify number of rows, suggesting " & n Rows, _ "Selection of Number of rows to Rotate", Selection. Count) n Rows = n Rows 1 'adjustment for inserts For i = n Rows To 2 Step -1 Rows(1). A check will be made that no cells contain data in the adjacent column to the right, but you can override this. Row If m Row 0 Then i Answer = Msg Box("Found non-blank in adjacent column -- " _ & Selection. You can remove the dependency by using copy and paste special value. for street numbers (leftmost word): =LEFT(TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160)," "))), SEARCH(" ", TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160)," "))))-1) for street names (remainder): =RIGHT(TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160)," "))), LEN(TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160), " "))))-SEARCH(" ", TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160)," "))))) Finding out what you actually have -- it may not be what it looks like (#debugformat) Formatting: Check what the cell was formatted for with Format, cells and look at the format.
You could have some non-breaking spaces in a formula -- see Trim ALL macro. Calculation = xl Calculation Manual 'pre XL97 xl Manual Dim cell As Range Dim ix as Long t Cells = Selection. Removing and converting characters may make record unsuitable to a database that is used to having certain characters as filler, which in some cases could include doubled spaces.
The limitation can be corrected better (come back in a year). Calculation = xl Calculation Automatic Application. Screen Updating = True End Sub Lastname() can be used to rearrange cells so that lastname appears first in cells in selected range. If there is a comma in a cell, the cell will be left alone; otherwise, the cell will be recomposed with the word after the last space first followed by a comma, and the first names. Sub Lastname() 'David Mc Ritchie 1999-04-09 ' cells in range in as Lastname, firstnames '--Application. This code is shorter and more efficient than the lastname macro because it was written later. Sub First Name() 'David Mc Ritchie 2000-03-23 programming ' Application. Calculation = xl Manual Dim cell As Range Dim c Pos As Long For Each cell In Selection.
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 in Slow Response. Generally to activate things you have changed the underlying formats. Also available on the code\page are Sep Last Name for people's names, Sep Last Word uses Str Reverse new in Excel 2000, Last Word function uses Str Reverse, resubstr function using VBA Script.
The fix USzip5 subroutine will repair the damage generally introduced by the Text to Data wizard or by software converting a scanned image to an Excel file.
Canadian zip codes are unaffected because they are not numeric.The TRIM Worksheet Function will remove code 32 space from left and right sides. CHR(160) is the non breaking space character ( ) in HTML which will be included by changing them first to normal spaces. Other characters you may see are TAB, CR, LF which have decimal values of 09, 13, and 10.