Catégorie : Excel function

The LEN() function returns the number of characters in a string. The LENB() function returns the number of bytes used by double-byte characters in a string.

Syntax
LEN(text)
LENB(text)

Arguments

• text(required): The text for which you want to determine the length.

Background
This function is useful for various tasks, such as combining it with other functions like MID(), LEFT(), or RIGHT() to manipulate strings.

You can use LEN() to verify whether an entry meets a specific length requirement or to check if text in a column exceeds a defined limit. The function counts spaces and numerals as characters.

Example
Suppose you need to ensure that interface descriptions in a column do not exceed 10 characters. You can use LEN() along with AutoFilter to quickly identify and correct strings longer than 10 characters.

Additional examples:

• =LEN(« CD »)returns 2.
• =LEN(« Excel 2007 »)returns 10.
• =LEN(« Microsoft »)returns 9.
• =LEN(« No Panic! »)returns 9.
• =LEN(«  »)returns 0.
• =LEN( » « )returns 1.
• =LEN(« 1.345 $ »)returns 7.
• =LEN(TODAY())returns 5.

The LEFT function returns the first characters of a string. The LEFTB function is used for double-byte characters and returns the first bytes.

Syntax
LEFT(text; num_chars)
LEFTB(text; num_bytes)

Arguments

• text(required): The string containing the characters you want to extract.
• num_chars/num_bytes(optional): Specifies how many characters/bytes to extract.

Background
Use the LEFT function to extract the first part of a string. You can enter letters or numbers in the text argument. The functions LEFT, RIGHT, and MID are especially useful if strings follow a specific pattern, such as ZIP codes, locations, or ISBNs.

The num_chars argument must be greater than or equal to 0. If num_chars exceeds the length of the text argument, LEFT() returns the entire string. If num_chars is omitted, the default value is 1.

Example
A list of names is entered into a spreadsheet column, with the first name separated from the last name by a space. Cell H4 contains a name.

The following formula extracts the first name:
=LEFT(H4; SEARCH( » « ; H4) – 1)

The SEARCH function determines the position of the space between the first and last names. Subtracting 1 gives the position of the last character of the first name (the number of characters from the left).

Additional Examples

• =LEFT(« steamboat »; 5)returns steam.
• =LEFT(« gazelle »; 4)returns gaze.
• =LEFT(« Oliver Kiel »; 5)returns Oliver.
• =LEFT(« Excel »; 1)returns E.
• =LEFT(« Excel »; 2)returns Ex.

This function converts numbers to formatted text with fixed decimal places and optional comma separators.

Syntax:
FIXED(number, [decimals], [no_commas])

Arguments:

• number (required): The numeric value to convert
• decimals (optional):
  • Positive: Rounds to specified decimal places
  • Negative: Rounds left of decimal point
  • Default: 2 decimal places
• no_commas (optional):
  • TRUE: Omits thousands separators
  • FALSE/omitted: Includes commas

Key Features:

1. Conversion Rules:
   • Maximum 15 significant digits
   • Supports up to 127 decimal places
   • Uses standard rounding (≥0.5 rounds up)
2. Comparison to Formatting:

Feature	FIXED()	Cell Formatting
Data Type	Text	Number
Calculations	Auto-converts	Direct
Display	Fixed width	Dynamic

Examples:

Assume that you want to ensure that a column with number values that is used for a mail merge in Word is not changed. Use the FIXED() function to convert the values into text in a new column. This column can then be used for the mail merge in Word. Here are some more examples:

=FIXED(12.56) returns 12.56.

=FIXED(1234.56,-1,1) returns 1230.

=FIXED(12.56,0) returns 13.

=FIXED(1234.56,-2,TRUE) returns 1200.

=FIXED(12.46,0) returns 12.

=FIXED(1234.56,-3,0) returns 1,000.

=FIXED(PI(),3) returns 3.142.

=FIXED(1234.56,-4,FALSE) returns 0.

Important Notes:

• For calculations, Excel automatically converts results back to numbers
• Alternative functions:
  • TEXT() for custom formats
  • DOLLAR() for currency formatting
• Combine with TRIM() to remove extra spaces

These functions locate the starting position of a substring within a text string. FIND() works with single-byte characters, while FINDB() handles double-byte characters (e.g., Asian languages). Both are case-sensitive.

Syntax:
FIND(find_text, within_text, [start_num])
FINDB(find_text, within_text, [start_byte])

Arguments:

• find_text (required): The substring to search for
• within_text (required): The text to search within
• start_num/start_byte (optional):
  • Character/byte position to begin search (default=1)
  • First position = 1 (not 0)

Key Features:

1. Case Sensitivity:
   • =FIND(« E », »excel ») → #VALUE!
   • =FIND(« e », »excel ») → 1
2. Error Conditions:
   • Returns #VALUE! if:
     • Substring not found
     • start_num ≤ 0
     • start_num > text length
     • Using wildcards (*, ?)

3. Special Cases:
   • Empty find_text («  ») returns start_num
   • Works with hidden characters

Comparison with SEARCH():

Feature	FIND()	SEARCH()
Case-Sensitive	Yes	No
Wildcards	No	Yes
Error Handling	Strict	Flexible

 

EXAMPLE

Assume that you have a string that is separated into two parts by an underscore. If you want to locate the uppercase S in the second part of the string and find its position in the string, you will first need to determine the position of the underscore to ensure that the first part is not searched.

Then you can perform the search in the remainder of the string. If cell C19 contains the string XLS2003_FormatCellSecure, the formula

=FIND(« S »,C19,FIND(« _ »,C19)+1)

returns 17, because the S in the second part of the string is located in the nineteenth position

Notes:

• For case-insensitive searches, use SEARCH()
• Combine with LEFT/RIGHT/MID for text extraction
• FINDB() essential for double-byte character systems

This function performs a case-sensitive comparison of two text strings, returning TRUE if they are identical and FALSE otherwise.

Syntax:
EXACT(text1, text2)

Arguments:

• text1 (required): First text string for comparison
• text2 (required): Second text string for comparison

Key Features:

1. Case Sensitivity:
   • =EXACT(« Word », « word ») → FALSE
   • =EXACT(« Word », « Word ») → TRUE
2. Format Ignorance:
   • Only compares raw text content
   • Ignores font styles, colors, or cell formatting
3. Array Compatibility:
   • Can compare a value against a range using array formulas

Practical Applications:

1. Data Validation:

=EXACT(A2, PROPER(A2))  // Checks for proper capitalization

2. Password Verification:

=EXACT(B1, B2)  // Compares two password entries

3. Array Search (Ctrl+Shift+Enter):

{=OR(EXACT(D22,B23:B48))}  // Checks if D22 exists in range

Comparison with Equal Sign (=):

Feature	EXACT()	= Operator
Case-Sensitive	Yes	No
Format-Aware	No	No
Array-Friendly	Yes	Limited

Examples:

Assume that after you have entered a list of data, you want to examine the array to check whether it contains a specific character string. The list is entered in the cell range B23:B48, and cell D22 contains the search string. Enter the following formula in a cell:

=OR(EXACT(C1;A2:A27))

and press Ctrl+Shift+Enter. The formula looks like this (see Figure 8-3):

{=OR(EXACT(C1;A2:A27))}

If you use the function as an array expression, you need the OR() function to return a single value from the list.

Here are a few further examples:

=EXACT(« Microsoft Excel », »Microsoft excel ») returns FALSE.

=EXACT(« steamboat », »steamboats ») returns FALSE.

=EXACT(« gazelle », »gazelle ») returns TRUE.

=EXACT(« John Smith », »Jeff Smith ») returns FALSE.

Limitations:

• Does not support wildcards (*, ?)
• For case-insensitive checks, use =A1=B1
• Array formulas require Ctrl+Shift+Enter in Excel 2019

This function converts a numeric value to text formatted as currency, using the local currency symbol and formatting conventions.

Syntax:
DOLLAR(number, [decimals])

Arguments:

• number (required):
  The numeric value to convert (can be a number, cell reference, or formula)
• decimals (optional):
  Number of decimal places to display:
  • Positive: Rounds to specified decimal places
  • Negative: Rounds left of decimal point
  • Omitted: Defaults to 2 decimal places

Key Features:

1. Conversion Behavior:
   • Converts numbers to text strings with currency formatting
   • Uses local currency symbol based on system settings
   • Default format: $#,##0.00;($#,##0.00)
2. Comparison to Cell Formatting:

Feature	DOLLAR()	Format Cells
Data Type	Text	Number
Display Overflow	Truncates	Shows ###
Calculations	Auto-converts	Directly usable

3. Rounding Rules:
   • Uses standard rounding (≥0.5 rounds up)
   • Negative decimals round to 10s, 100s, etc.

Examples:

 Assume that you want to ensure that a price column used for a mail merge in Microsoft Word is not changed. For this reason, you use the DOLLAR() function to convert the values in the price column into the corresponding currency text in a calculated column. The calculated column is used for the mail merge process in Word. Here are some other examples:

=DOLLAR(12.56) returns $12.56.

=DOLLAR(38612.60,-1) returns $38,610.

=DOLLAR(12.56,0) returns $13.

=DOLLAR(38612.60,-2) returns $38,600.

=DOLLAR(12.46,0) returns $12.

=DOLLAR(38612.60,-3) returns $39,000.

=DOLLAR(PI(),3) returns $3.142.

=DOLLAR(38612.60,-4) returns $40,000.

As shown below;