How To Use the LEN Formula in Excel To Highlight Issues With A Data Import - Excel Tip

The LEN equation truly is one of the center content recipes to have in your Excel Toolbox. It is utilized to restore the length of a predefined content string I.e it includes the quantity of characters in the bit of content.

Language structure of the recipe is =LEN(text)

So how might you utilize this equation?. I have utilized it to recognize issue with the length of database fields when moving starting with one database then onto the next. In my old database the location line field was not so much confined and could contain up to 255 characters. My new database is currently limited to 30 characters for the location field, so I expected to distinguish any issued with the information import before it occurred. Utilizing Excel LEN capacity did only that. Here is the ticket-

Open your Excel document with the information send out for my situation it was address table

Addition an additional section to permit is to populate it with the aftereffects of the LEN recipe, for this situation I have embedded a segment to right of Column C which contained my location information

Name the section so we realize what it is being utilized for

Recipe tab, Insert Function or select the primary cell in the segment and hit = to start the equation

Select LEN or type LEN the open sections (

Snap on the phone you need to decide the length of string (in this model it is in section B)

In the event that utilizing the equation wizard Excel will show the appropriate response promptly, or close sections and hit OK to physically wrap up the recipe

All we have to do is duplicate the equation down the entire segment

Look down with your mouse and SHIFT-click in the last cell in your section

Press Ctrl+D

We currently realize the character check of segment B, which is presently shown in segment C. How about we feel free to feature any of the character tally cells that are more than 30 characters with contingent organizing.

Select Column C cells

Home Tab-Style Group

Restrictive Formatting

Feature Cell Rules

Select Great Than-we are searching for any cells that are more prominent than 30 characters so type 30 and select the kind of organizing I will leave as the default show

Hit OK

There we go, every one of the cells that will cause issued are featured and can be settled before information import.

A couple of focuses to note on LEN.

The LEN capacity will tally and restore the complete number of characters for:

content strings

number information

extraordinary characters -, for example, hyphens, ampersands, and percent signs

non - printing characters

space characters - including the spaces between words

Occupation done in two or three ticks. All issue tends to related to an Excel content capacity.