Background
VBA has a Format() function which converts any value to its textual representation. This is useful for numbers and Dates and times, but it can also be used on Strings.
Debug.Print Format("1234567890abc", "(@@@) @@@-@@@@ ext. @@@")
(123) 456-7890 ext. abc
Now what I want to do is something like this...
Debug.Print Format("example text", "\E\n\t\i\r\e\ \s\t\r\i\n\g\:\ \""@\""\.")
Entire string: "example text".
...but instead I get the following result. Note how only a single character displays where intended. Also, the other characters are stuck onto the end.
Entire string: "e".xample text
The documentation suggests we can only display individual characters in sequence. Unlike printf() with %s and friends, this prevents dynamic formatting of the text as a whole, and forces us to hard-code the number of characters into our format.
| Character | Description |
|---|---|
@ |
Character placeholder. Display a character or a space. If the string has a character in the position where the at symbol (@) appears in the format string, display it; otherwise, display a space in that position. Placeholders are filled from right to left unless there is an exclamation point character (!) in the format string. |
& |
Character placeholder. Display a character or nothing. If the string has a character in the position where the ampersand (&) appears, display it; otherwise, display nothing. Placeholders are filled from right to left unless there is an exclamation point character (!) in the format string. |
< |
Force lowercase. Display all characters in lowercase format. |
> |
Force uppercase. Display all characters in uppercase format. |
! |
Force left to right fill of placeholders. The default is to fill placeholders from right to left. |
Question
Is there a native option to Format() an entire string, rather than its individual characters?
Technically, we could implement our own UDF called FormatString(). This identifies a designated character (like *) in the format which is not escaped (\*), and substitutes as many placeholders ("@@@…@") as there are characters in the input. Then it passes the input and modified format to the native Format().
However, this is fraught with challenges like identifying escape sequences: * versus \* versus \\* versus \\\* and so forth. As such, I absolutely desire a native solution rather than a UDF, which introduces instability and complexity.
evaluate("=REGEXTEST(...")of course quotes get annoying...Format("example text", "\E\n\t\i\r\e\ \s\t\r\i\n\g\:\ \""" & string(100,"&") & "\""\.")would work but it's unclear what you actual use cases are and it seems like there could be simpler ways to do what you want - some of that "complexity" (escape sequences etc) seems to be the result of (mis)usingFormat? Similar post: stackoverflow.com/questions/17233701/…Expressionis the biggest limiting factor here. The biggest limitation I see is that theFormatargument can't take more than 255 characters, so the highest you can go isstring(219,"&")in this example:debug.print Format(string(999999999,"a"), "\E\n\t\i\r\e\ \s\t\r\i\n\g\:\ \""" & string(219,"&") & "\""\.")example textentered in a cell in the UI, to which the custom number format;;;"Entire String: "\"@\""."has been applied, calling.Texton that cell would returnEntire String: "example text".. Of course, the 32,767 character limit for a cell's contents is tiny in terms of how large a VBA string can be, but there are billions of cells available on a worksheet.Formatthe object model also hasText, e.g.Debug.Print WorksheetFunction.Text("example string",";;;""Entire String: ""\""@\"""".""")(i.e. just doubling up on the quotes from the custom-number format - the 3 semicolons can actually be deleted)