Subscribe for New Post Notifications

Showing posts with label Text Function. Show all posts
Showing posts with label Text Function. Show all posts

How to use the SUBSTITUTE function in Microsoft Excel

Let's look at some Excel Substitute function examples and explore how to use the Substitute function as a worksheet function in Microsoft Excel:

SUBSTITUTE function

Summary

The SUBSTITUTE function is as similar to the REPLACE function used in the excel. However, SUBSTITUTE function replaces one or more instances of a substituted text string with a specified character(s). Whereas REPLACE function only replaces the text in a specified position/location of a supplied string.
Substitute function can be useful for when you want to substitute old text with a new text in the given text. For example, if the given text is 9988-776-655 and you want it as 9988776655 then you can use SUBSTITUTE function to transform this.
The SUBSTITUTE function is a built-in function in Excel that is categorized as a Text Function.

What Does It Do?

Replace text based on content.

Return value

The string/text Value.

Syntax

=SUBSTITUTE (text, old_text, new_text, [instance_num])

Parameters or Arguments

text - The original string to use to perform the substitution.
old_text - The text to replace.
new_text - The new text to replace with.
instance_num - The instance to replace. If omitted, every occurrence of the old text will be changed to the new text.

Formatting

No special formatting is needed.

Substitute different characters with a single formula

You can nest several SUBSTITUTE functions within a single formula to do several substitutions at a time, i.e. substitute multiple characters with a single formula.

Below are the example of this.

Nested Substitute

Usage notes

  • The Excel Substitute function can replace text by matching. Use the Substitute function when you want to replace text based on its content, not position. Optionally, you can specify the instance of found text to replace (i.e. first instance, second instance, etc.).
  • Substitute function finds and replaces old_text with new_text in a text string.
  • If you specify an instance number, then only that instance of the old text is replaced. Otherwise, every occurrence of old text in the text string is changed to new text.
  • Use SUBSTITUTE function to replace text based on content. Use the REPLACE function to replace text based on its location.
  • Substitute function is case-sensitive.
  • Substitute function does not support wildcards.

No comments:
Marcadores: , ,

How to use the REPLACE function in Microsoft Excel

Let's look at some Excel Replace function examples and explore how to use the Replace function as a worksheet function in Microsoft Excel:

REPLACE function in Microsoft Excel

Summary

The Excel REPLACE function replaces characters specified by position in a given text string with another text string.
The REPLACE function is a built-in function in Excel that is categorized as a Text Function.

What Does It Do?

Replaces a portion of text with a new piece of text.

Return value

The altered text.

Syntax

=REPLACE (old_text, start_num, num_chars, new_text)

Parameters or Arguments

old_text - The original string value.
start_num - The starting position in the text to replace.
num_chars - The number of characters to replace in old_text.
new_text - The text to replace old_text with.

Formatting

No special formatting is needed.

Usage notes

The REPLACE function is useful for replacing text at a known position in a given string. For example, the following formula replaces 4 characters starting at the 1st character:

=REPLACE("Tech World",1,4,"Information")//
returns "Information World"

  • #NAME? - Occurs if text data entered as the Old_text argument is not enclosed in double quotation marks - row five above.
  • #VALUE! - Occurs if the Start_num or Num_chars arguments are negative or contain non-numeric values - row eight above.
  • Use the REPLACE function when you want to replace text based on a known location.
  • Use FIND or SEARCH to find and replace text when the location is not known in advance.
  • Use SUBSTITUTE to replace one or more instances of text based on content only.

No comments:
Marcadores: , ,

How to use the SEARCH function in Microsoft Excel

Let's look at some Excel Search function examples and explore how to use the Search function as a worksheet function in Microsoft Excel:

SEARCH function

Summary

The SEARCH function is a way to find a character or string within another cell, and it will return the value associated with the starting place. In other words, if you are trying to figure out where a character is within the cell that contains a word, sentence or other type of information, you could use the SEARCH function.
SEARCH returns the position of the first character of find_text inside within_text. Unlike FIND, SEARCH allows wildcards, and is not case-sensitive.
The SEARCH function is a built-in function in Excel that is categorized as a Text Function.

What Does It Do ?

Get the location of text in a string.

Return value

A number representing the location of find_text.

Syntax

=SEARCH(find_text, within_text, [start_num])

Parameters or Arguments

find_text - The text to find.
within_text - The text to search within.
start_num - Starting position in the text to search. Optional, defaults to 1.

Formatting

No special formatting is needed, the result will be shown as a number.

Usage notes

  • Use the SEARCH function to get the location of one text string inside another.
  • SEARCH returns the position of the first character of find_text inside within_text.
  • SEARCH function is not case-sensitive, the upper-case and lower-case find_text values, "M" and "m", return the same result.
  • SEARCH function allows using the wildcard characters.
  • SEARCH allows the wildcard characters question mark (?) and asterisk (*), in find_text.
  • The ? matches any single character and the * matches any sequence of characters. To find a literal ? or *, use a tilde (~) before the character, i.e. ~* and ~?.
  • If the SEARCH function does not find a match, it will return a #VALUE! error.

No comments:
Marcadores: , ,

How to use the FIND function in Microsoft Excel

Let's look at some Excel Find function examples and explore how to use the Find function as a worksheet function in Microsoft Excel:

FIND function

Summary

The FIND function in Excel is used to return the position of a specific character or sub-string within a text string.
The FIND function is a built-in function in Excel that is categorized as a Text Function.

What Does It Do ?

Get the location of text in a string.

Return value

A number representing the location of find_text.

Syntax

=FIND(find_text, within_text, [start_num])

Parameters or Arguments

find_text - The text to find.
within_text - The text to search within.
start_num - The starting position in the text to search. Optional, defaults to 1.

Formatting

No special formatting is needed, the result will be shown as a number.

Usage notes

  • If the find_text argument contains several characters, the FIND function returns the position of the first character. For example, the formula FIND("en","sentence") returns 2 because "e" in the 2nd letter in the word "sentence".
  • If within_text contains several occurrences of find_text, the first occurrence is returned. For example, FIND("l", "Ball") returns 3, which is the position of the first "l" character in the word "Ball".
  • If find_text is an empty string "", the Excel FIND formula returns the first character in the search string.
  • The location is returned as the number of characters from the start of within_text.
  • Start_num is optional and defaults to 1.
  • The Excel FIND function returns the #VALUE! error if any of the following occurs:
    • =>Find_text does not exist in within_text.
    =>Start_num contains more characters than within_text.
    =>Start_num is 0 (zero) or a negative number.
  • The FIND function is case sensitive and does not allow wildcard characters.
  • If you are looking for a case-insensitive match, use the SEARCH function and/or to use wildcards.

No comments:
Marcadores: , ,

How to use the TEXT function in Microsoft Excel

Let's look at some Excel Text function examples and explore how to use the Text function as a worksheet function in Microsoft Excel:

TEXT function

Summary

Text function in excel is basically used to convert the numeric values into a text format that is specified. So, it takes a value and turns it into text based on the numeric format of your choice.
The Text function is a built-in function in Excel that is categorized as a Text Function.

What Does It Do ?

This function converts a number to a piece of text. The formatting for the text needs to be specified in the function.

Return value

A number as text in the given format.

Syntax

=TEXT(value, format_text)

Parameters or Arguments

value - The numerical value that we need to convert to text. format_text - The format we want to apply.

Formatting

No special formatting is required.

When is the Excel TEXT Function required?

  1. We want to display dates in a specified format.
  2. We wish to display numbers in a specified format or in a more legible way.
  3. We wish to combine numbers with text or characters.

Usage notes

  • Use the TEXT function to convert a number to text in a specific number format.
  • The data converted into text cannot be used for calculations. If needed, we should keep the original data in a hidden format and use it for other formulas.
  • TEXT is especially useful when you want to embed the numeric output of a formula or function and present it in a particular format inside other text. For example, "Sales last year increased by over $10,500", where the number 42100 has been formatted with a currency symbol and thousands separator.
  • The TEXT function returns the #NAME? error if you omit the quotation marks around the format code. For example the formula =TEXT(A2, mm/dd/yy) is incorrect and should be written this way: =TEXT(A2, "mm/dd/yy").
  • The TEXT function in Excel is language-specific, and requires using region-specific date and time format codes.
  • format_text must appear in double quotation marks.

No comments:
Marcadores: , ,

How to use the REPT function in Microsoft Excel

Let's look at some Excel Rept function examples and explore how to use the Rept function as a worksheet function in Microsoft Excel:

REPT function

Summary

The Microsoft Excel REPT function returns a repeated text value a specified number of times. For example, =REPT("ABC",3) returns "ABCABCABC".
In financial analysis, This can be useful if you want to fill a cell, or pad values to a certain length. We can also build histograms, a chart commonly used in financial modeling, using the function by translating values directly into a certain number of (repeated) characters.
The Rept function is a built-in function in Excel that is categorized as a Text Function.

What Does It Do ?

Repeats a piece of text a specified number of times.

Return value

The repeated text.

Syntax

=REPT (text, number_times)

Parameters or Arguments

text - The text value to repeat.
number_times - It is the positive number that specifies the number of times to repeat text.

Formatting

No special formatting is needed.

Usage notes

  • The number_times parameter of the REPT function must be a positive number.
  • The result of the REPT function cannot be longer than 32,767 characters, or else it will return a #VALUE! error.
  • If number_times is not an integer, it is truncated. For example, if you use the number 10.8, it will only consider 10 and repeat the specified text 10 number of times.
  • If number_times is 0 (zero), REPT returns “” (empty text).

The REPT function repeats characters a specified number of times. For example, to repeat "A" six times, you can use the following formula:

=REPT("A",6) // returns "AAAAAA"

Inputs to REPT can be variable. In the example shown, REPT is configured to repeat the string in column B using the count in column C. The formula in D5 is:

=REPT(B5,C5) // returns "@@@@@"

No comments:
Marcadores: , ,

How to use the EXACT function in Microsoft Excel

Let's look at some Excel Exact function examples and explore how to use the Exact function as a worksheet function in Microsoft Excel:

EXACT function

Summary

The Excel EXACT function compares two text strings, The case of the characters is taken into account, only words which are slept the same and which have upper and lower case characters in the same position will be considered as equal. EXACT is case-sensitive.
In financial analysis, we often need to compare two values while analyzing data and the EXACT function helps us to do the comparison.
The Exact function is a built-in function in Excel that is categorized as a Text Function.

What Does It Do ?

This function compares two items of text and determine whether they are exactly the same.

Return value

Returns TRUE if the two strings are the same.
Returns FALSE if the two strings are different.

Syntax

=EXACT(text1, text2)

Parameters or Arguments

text1 - The first text string to compare.
text2 - The second text string to compare.

Formatting

If the two items of text are exactly the same the result of TRUE will be shown.
If there is any difference in the two items of text the result of FALSE will be shown.

Usage notes

  • The EXACT function is case-sensitive when it compares the two strings but ignores formatting differences (ex. 0.08 = 8%). You can use EXACT to test text being entered into a document.
  • If the two strings are exactly the same, EXACT returns TRUE. If the two strings are not the same (taking into account upper and lower case) EXACT returns FALSE.
  • Erroneous spaces at the beginning or end of the text string will result in a value of FALSE. Use the TRIM Function to remove erroneous spaces <>.

Examples

Below are two examples of the EXACT function used with hard coded strings. In the first example, the strings are identical, in the second example, the only difference is the capital "G":

=EXACT("Good","Good") // returns TRUE
=EXACT("Good","good") // returns FALSE

You can also use a normal equals sign (=) in a formula, but the comparison is not case sensitive:

=("Apple"="apple") // returns TRUE

You can use this result inside the IF function to make a conditional calculation. To display the message “Yes” or “No” for a match.

For Example, =IF(EXACT(A2,B2),”Yes”,”No”).

No comments:
Marcadores: , ,