Start a new topic

Count of Number of Characters in a Cell in Excel

When you are importing data into Raiser's Edge there are character limitations on some fields.  For example, the Birth Place field in Raiser's Edge only allows a total of 50 characters.  You can identify potential import errors by counting the number of characters in a cell in Excel.   To count the number of characters in a cell in Excel, please use the following steps:


1.    Find the cell or cells for which you need to calculate the number of characters.

2.    Insert a new column adjacent to the column you need to count.

3.    Place your cursor in the cell adjacent to the one whose characters you want to count.  For instance, if you want to count the number of characters in cell A1, place your cursor in the  cell.

4.    Type the formula "=Len(A1)" and press the "Enter" key. The number of characters in cell A1 are displayed in cell B1.

5.    Copy the formula to any additional rows by highlighting the cell containing the formula, holding the "Ctrl" key down and pressing the "C"key. Place the cursor on the next row, hold the "Ctrl" key down and press "V" to paste the formula. The row assignment will change automatically when the formula is copied. For instance, if you copy the formula"=Len(A1)" from cell B1 to cell B2, the formula in cell B2 will read"=Len(A2)."

image



If you have any additional questions about field requirements for imports, please refer to the Raiser's Edge Import Guide.


You can also add a dictionary to cut down the length of strings:

$1 -> (^.{10})(.*)

Where 10 is the maximum characters
That is a great suggestion, Wayne. Thanks for sharing!

Clarifying Wayne's suggestion - because I wasn't clear on how to populate the dictionary, but I figured it out.

The $1 goes in the Replacement Values side.

Ignore the ->

Add the part of in parenthesis, including the parenthesis, to the Values to Match on.

And you must check the box for "Use Regular Expressions"


Here's a screen shot with 50 characters in this example:

image




The above formulat doesn't work for me.  I think it should actually be...

^(.{0,50})(.*)$


Login to post a comment