User Tools

Site Tools


handy_excel_tips_and_tricks

I have some Excel code that helps me manipulate an excel worksheet. Some of these techniques are very important in making the data usable without the hours or days in could take to fix by hand.

In order to use most of the techniques here you must place the code for these functions and procedures in a module. To do this I choose Tools > Macro > Visual Basic Editor. Then I right click on the project within the project window > Choose Insert > Then Choose Module. I immediately save, and the module is now ready for me to place any functions/procedures or macros into it. All I have to do is paste the code in the bottom of the module window. Click or double click on the module you created in the project window to see its contents.

Challenge 1: I have a bunch of numbers that I need to run use in a formula, but they have leading zero's and can not be used in the formula. Solution: In order to get these columns into a numerical format there are several ways this might work. The most flexible way would be to take advantage of a Visual Basic for Applications (VBA) function named “CInt”. Cint is used to convert text to its integer counterpart.


Public Function cmCInt(var1)

  cmCInt = CInt(var1)

End Function


This Technique is called wrapping. Usage: The routine or function named cmCInt wraps the VBA function CInt.

Extra Information: Excel has many built in functions that you can use to do everything from simple arithmetic to complete financial calculations. When the basic excel tools can not do the job we can use VBA. Whenever you write a macro or other routine in Excel it uses VBA.

Usage: in the formula of a cell you want to show the numeric version of a text field like “00012”, you would put this:


=cmCInt(A2)


Where A2 is the field containing 00012 as a string.

Challenge 2: Getting the formula results to become the formula. How do I fill the column with the formula results so they don't use the formula any more? You would do this If you want the cells in a column to become permanent and not change as the cells the formulas point to change. Solution: In order to get the formula results of all selected cells in a column we will copy the results into the adjacent cell's formula. Then the adjacent column can replace the current column.


Sub CopyToTheRight()

  
  Dim ra As Range
  Set ra = Application.Selection
  Dim r1, c1, r2, c2
  r1 = ra.Row
  c1 = ra.Column
  Dim Rowcount, ColCount
  Rowcount = ra.Rows.Count - 1
  ColCount = ra.Columns.Count - 1
  r2 = r1 + Rowcount
  c2 = c1 + ColCount
  
  If ColCount > 1 Then
      MsgBox "Can not select more than 1 column for this operation. Stopping Routine."
      Exit Sub
  End If
  
  Dim i
  
  Dim var1 As Sheet1
  
  Set var1 = Application.ActiveSheet
  For i = 1 To Rowcount
      var1.UsedRange.Cells(r1 + i - 1, c1 + 1) = ra.Cells(i, 1)
  Next
  

End Sub


Usage: Select a range of cells in a single column and run the macro CopyToTheRight.

Another handy function to wrap is the VBA function “InStr”. InStr has 3 arguments: 1) Start Position; 2) String to search on; 3) String to search for. Wrapper Function:


Public Function cmINSTR(var1, var2, var3)

  cmINSTR = InStr(var1, var2, var3)

End Function


Usage (Cell Formula):


=cminstr(1,C2,“ABC”)


This will give you the first occurance of “ABC” in cell C2. If C2 contains “123ABC” then the result will be 4. If The string to search for does not appear in the cell you are pointing at a -1 will be the result.

There is power in these simple Techniques

  When these techniques are used in conjuction with normal excel functions you can easily overcome previous limitations. Have you ever had excel tell you that you can't perform a formula because part of the formula points back at the current cell you are working with (Called a circular reference). Using the CopyToTheRight procedure above you can copy the results to the adjacent column and avoid this problem. Challenge 1 above helps you remove leading zero's, and the VBA function instring can help you find all records that contain a certain word or character.

If you have any excel hints and tips, please send an email to [email protected] and i'll add them to the knowledge base. If you are having trouble creating your own excel routine, I might just have a valuable suggestion for you.

Challenge 3: Turning multiple lines of information into a single record In some instances you may have a description field that spans several Rows. It can be quite difficult to get these seperate rows to be a single record. I will outline a situation and a method by which you can fix the multi-line record problem.


There are 4 fields in this spreadsheet: * Field 1 is The unique Identifier and counts from 1 trough 999 * Field 2 is The Record Identifier. There are 3 lines per record and the record numbers count from 1 through 333. * Field 3 is has the text “000” in the first of the 3 possible lines taken up by a record. * Field 4 is a 50 character description field * Field 6 contains the length of the long descrpiption The simple way to solve this problem would be to use a fourth row with the formula for cell E2 looking like this: =D2 & D3 & D4. We fill this formula down the whole column, and then sort descending the spreadsheet by field 3 (not including the header row if it exists). This will give us all the “000” records at the top of the page, and column 5 will contain the full (up to 150) character descriptions.

This is a fairly simple situation you can easily create and test. Problems arise when some records consist of 1, 2, 3 records or even more. Now all of the records are not 3 lines long and our formula doesn't work. In this case there are a couple solutions. The one that scales the best (ie works with a high number of different lines per record) will be described here.

Information: There are more elegant and faster running routines available if you can program a VBA script or Macro to do your bidding, but that is not an option for most users.

Solution for more “Difficult” data:

We will apply the things we learned in the previous articles and in the simple solution. The simple solution points to a formula which concatinates (Or joins) the 3 seperate lines into one will still be used, but only if the length of the long description (held in field 6) is less than a certain length. This restriction comes into play because we are going to run through our procedure once for each line within the longest record. So if the longest record covers 5 lines then we will need to go through our procedure 5 times to get the correct descriptions.

* First we need to insert 2 records next the the current description field. * We then use a formula fill this column with the longest description. Any records that have less lines than the maximum description record size will have “Garbage” appended to the end in the form of descriptions from other items. This is expected. =IF(F3 < 151,G4&G5&G6,H3) Information: The function here searches for a termination character instead of using the field that contains the overall length. If you do not have a termination character, you can either add one or change the formula to use the length field. Caution: This formula does not correspond to the exact field numbers that would be created following this example a link to the example excel spreadsheet will be provided here in the future. * We now use the CopyToTheRight Macro Defined in Challenge 2 above to copy the results of the formula to the second column. * Now we change the formula used to get this round of descriptions a little to do the next round. * We continue this process until complete.


LD2.xls

Here is some code you could use to Pad strings with zero's or whatever else you would like to pad them with.

' Usage:
' msgbox Pad(4, 3, False) ' Result: 004
' msgbox Pad("MI", 3, False) ' Result: 0MI


' for the next example, field A1 = "4"
' =Pad(A1, 3, False)
' Result in field containing formula would be: 004


' for the next example, field A1 = "MI"
' =Pad(A1, 3, False) 
' Result in field containing formula would be: 0MI


Function Pad(numVar, nLen, bAddLen)
    Dim i
    Dim strZeros
    strZeros = ""
    Dim lenStrVar
    lenStrVar = CStr(CNull(numVar, ""))
    
    If Len(CStr(nLen)) > 2 Then
        Err.Raise 1002, "Pad", "Cannot have a length string longer than 1 character"
    End If
    For i = 1 To nLen - Len(lenStrVar)
        strZeros = strZeros & "0"
    Next
    If bAddLen Then
        Pad = CStr(nLen) & strZeros & (lenStrVar)
    Else
        Pad = strZeros & lenStrVar
    End If
End Function



' CNull is a handy function used to prevent errors in above routines when bad/empty data is entered into the function
Public Function CNull(varIn, DefValue)
    If IsNull(varIn) Then
        CNull = DefValue
    Else
        CNull = varIn
    End If
End Function

* using CONCATENATE to build picture import based on System Five Unique number

  =IF(LEN(A1)=1,CONCATENATE("ip00000",A1,".jpg"),IF(LEN(A1)=2,CONCATENATE("ip0000",A1,".jpg"),IF(LEN(A1)=3,CONCATENATE("ip000",A1,".jpg"),CONCATENATE("ip00",A1,".jpg"))))))

Remove Character Function

This routine below can be used to remove all occurances of a character from a cell in Excel. Go here for video on the “Remove Character Excel Routine

Function cmRemChar(cell1 As Range, char1 As String)

    Dim instr1 As Integer
    Dim tmpStr As String
    
    tmpStr = cell1.Value
    instr1 = InStr(1, tmpStr, char1)
    
    Do While instr1 > 0
        tmpStr = Left(tmpStr, instr1 - 1) & Mid(tmpStr, instr1 + 1)
        instr1 = InStr(1, tmpStr, char1)
    Loop

    cmRemChar = tmpStr
    
End Function

A Very Simple Function to Restore Leading Zeros in Bar Codes

If there are leading 0s in a bar code then excel drops them if it sees the bar code as a number. Often the leading check sum digit is a 0. Example 012345123457 will show as 12345123457 and after data load, will not scan.

Quick fix: Determine the length of the bar code with check sums in place, most often 12. Create a calculation column next to the broken bar codes and fill with the function =TEXT(Cell,“000000000000”) (cell is the cell reference ie A1)

The result is a 12 character text string with how ever many 0's required in the first places.

For bar codes other than 12 digits, change the number of 0's in the quotations to return the desired string length.

Sample way to replace INSTR VBA functionality

See article below copied from web at http://www.mrexcel.com/forum/excel-questions/76765-equivalent-instr-instrrev-excel.html. Thanks to the MRExcel website as well pnhughes the user who answered the question I was able to find by searching on Google. Maybe with this link / article here, I will remember the “Find” function instead of having to Google it :) .

 Re: Equivalent of InStr and InStrRev in excel
There is a way to do this using Excel native functions. It's a bit awkward, but will work.

If you want to perform a logical test for the presence or absence of a character in a string, 
FIND and SEARCH will let you down. Both functions return '#VALUE!' if the search string is not 
found, which in turn blows the results of your logical test. Instead, you can use the LEN and 
SUBSTITUTE functions to perform your logical test.

I'll use an example of a 'Full Name' column I'm trying to break down into 'First Name' and 
'Last Name'. The catch is that my 'Full Name' column is sometimes formatted as 
'Lastname, Firstname', and sometimes as 'Firstname Lastname', but I want to parse it cleanly 
regardless of the format of the original column.

Based on the presence or absence of a comma in the 'Full Name' column, I can reliably break 
out my desired components most of the time. FIND or SEARCH won't work here, for the reasons 
above. However, I can use SUBSTITUTE to replace all instances of a comma in my 'Full Name' 
string with... nothing. Doing so will return a shorter string than the original if any commas 
are present, and a string the same length as the original if no commas are present. There is 
no '#VALUE!' result waiting for me here.

So, for the string 'Simpson, Homer' in cell A4:
FIND(",", A4) returns a value of 8
LEN(A4) returns a value of 14
LEN(SUBSTITUTE(A4, ",", "")) returns a value of 13

For the string 'Homer Simpson' in cell A4:
FIND(",", A4) returns '#VALUE!'
LEN(A4) returns a value of 13
LEN(SUBSTITUTE(A4, "," "")) returns a value of 13

So, by comparing the lengths of the strings for your logical test, as below:
Code:
IF(LEN(SUBSTITUTE(A4, ",", ""))=LEN(A4), do_when_comma_is_absent, do_when_comma_is_present)
you get a reliable result without having to bust out the VBA.
handy_excel_tips_and_tricks.txt · Last modified: 2012/09/04 13:29 (12 years ago) by cliff