Windy.Text

Antonio Nakić-Alfirević · Featured, Functions
A package containing Excel functions for text manipulation.

Description

A collection of Excel functions for text manipulation:
  • Windy.Format
  • Windy.FormatWithCulture
  • Windy.IndexOf
  • Windy.IsMatch
  • Windy.RegexReplace
  • Windy.RegexSplit
  • Windy.RegexTransform
  • Windy.ReplaceAt
  • Windy.Split
  • Windy.Substring
  • Windy.ToCurrencyString
  • Windy.ToOrdinalWords
  • Windy.ToWords
Windy-Text

Definitions and examples


Windy.Format

Replaces the format items in a given input string with the string representations of corresponding objects.

Example:

The following formula displays a sentence specifying the number of items sold for some price based on the value of the F12 and F13 cells.

=Windy.Format("The company sold {0,10} items for {1:C}",F12,F13)

When F12 or F13 change, the value is automatically updated. The sentence is formatted to have an offset for the number of items and to display the number in F13 as currency. If F12 is 25 and F13 is 5500, the return value is:

The company sold 25 items for $5,500.00

More about formatting can be found in the function's C# counterpart documentation.


Windy.FormatWithCulture

Replaces the format items in a given input string with the string representations of corresponding objects. The first parameter supplies culture-specific formatting information (e.g. en-US, de-DE, hr-HR…).

Example:

The following formula displays a sentence specifying the number of items sold for some price based on the value of the F12 and F13 cells. We also specify that the formatting culture is hr-HR.

=Windy.FormatWithCulture("hr-HR", "The company sold {0,10} items for {1:C}",F12,F13)

When F12 or F13 change, the value is automatically updated. The sentence is formatted to have an offset for the number of items and to display the number in F13 as the specified culture's currency. If F12 is 25 and F13 is 5500, the return value is:

The company sold 25 items for 5.500,00 kn

More about formatting can be found in the function's C# counterpart documentation.


Windy.IndexOf

Returns the zero-based index of the first occurrence of a specified string within the given input string. The method returns -1 if the character or string is not found in the given input string.

Example:

The following formula displays the index of the first occurence of the string specified in cell F19 in the string Highway to the Danger Zone.

=Windy.IndexOf("Highway to the Danger Zone", F19)

For instance, if the value in F19 is Danger, the formula returns the number 15. But if F19 is Zone of Danger, the return value is -1 because it can't be found in the initial string.


Windy.IsMatch

Indicates whether the specified regular expression finds a match in the given input string.

Example:

The following formula checks whether the \d regular expression has any matches in the value of cell A24.

=Windy.IsMatch(A24, "\d")

So, if the value of cell A24 is 123, the formula returns TRUE. If A24's value is abc, the formula returns FALSE. But if the value of cell A24 is abc123, the formula returns TRUE, as it is able to find a match.


Windy.RegexReplace

Replaces all substrings of an input string that match the given regular expression pattern. Replaces the substrings with the specified replacement string.

Example:

This formula example replaces all minor currency units from the value of cell A26 with an empty string.

=Windy.RegexReplace(A26,",\d+", "")

If the value of cell A26 is 13.245,17 - the return value of the formula will be 13.245.


Windy.RegexSplit

Splits an input string into an array of substrings at the positions defined by a regular expression.

Example:

The following formula splits the comma separated values from cell A33 into multiple row cells - each cell for one value. If we had provided the last argument with the value TRUE, the formula would split the values into multiple column cells.

=Windy.RegexSplit(A33,",")

For instance, if A33's value is 1,5,14,23,31 and we write the formula from the example in B33, this would result in the following:


Windy.RegexTransform

Returns a new string based on the specified template that is populated by matches from the input string.

Example:

The following example parses a URL from cell A56 into a sentence describing the URL's protocol, host name and segment.

=Windy.RegexTransform(A56,"^((?'protocol'[^:/?#]+):)?(//(?'host'[^/?#]))?(?'path'[^?#])(\?([^#]))?(#(?'segment'.))?", "The URL protocol - ${protocol}, host name - ${host}, segment - ${segment}")

If the value in cell A56 is https://querystorm.com/extensions/windy-text/#regex-transform, then the result value of the formula is The URL protocol - https, host name - querystorm.com, segment - regex-transform.


Windy.ReplaceAt

Returns a new string in which a given number of characters starting from the specified index are replaced with the specified replacement string.

Example:

This formula example replaces the first 10 characters of the string in cell G36 with the value Ride into.

=Windy.ReplaceAt(G36, 0, 10,"Ride into")

So if the value of cell G36 is Highway to the Danger Zone, the result value of the formula would be Ride into the Danger Zone.


Windy.Split

Divides text around a specified character or string and puts each fragment into a separate cell in the row.

Example:

The following example splits the value from cell A39 into cells in the row. If we had provided the last argument with the value TRUE, the formula would split the values into multiple column cells.

=Windy.Split(A39," ")

If the value in A39 is Sterling Malory Archer and we write the above formula in cell B39, this would result in the following:


Windy.Substring

Retrieves a substring from the input string that starts from the given index.

Example:

The following formula returns a substring from the value of cell G36 starting from the specified index.

=Windy.SubString(G36,15)

For instance, if the value of cell G36 is Highway to the Danger Zone, the formula result value would be Danger Zone.


Windy.ToCurrencyString

Format a number as money according to the given culture identifier (e.g. en-US, de-DE, hr-HR…).

Example:

This formula example takes a number from cell A19 and converts it to a currency according to the given culture identifier.

=Windy.ToCurrencyString(A19, "hu-HU")

If the value of cell A19 is 117, the formula's result value would be 117,00 Ft.


Windy.ToOrdinalWords

Converts a number into its ordinal word representation according to the given culture (e.g. 1 => one).

Example:

The following formula converts a number from cell D23 into its word representation according to the provided culture.

=Windy.ToOrdinalWords(D23, "de-DE")

If cell D23 had a value of 23, the formula's value would be dreiundzwanzigster.


Windy.ToWords

Converts a number into its word representation according to the given culture (e.g. 1 => first).

Example:

The following formula converts a number from cell D23 into its word representation according to the provided culture.

=Windy.ToWords(D23, "de-DE")

If cell D23 had a value of 23, the formula's value would be dreiundzwanzig.