To get the third character from a string in A1, you can use the MID function like this:
=MID(A1,3,1)
The first argument is a cell reference, the second argument specifies the start number, and the third argument...
This is an experimental formula to strip characters from text. The experimental part is using character codes instead of regular characters as a way to make the formula case-sensitive, and providing a way to reverse the...
Note: in this example, we are calculating the end position in order to extract a substring with a literal start and end position. However, if you know the number of characters to extract, you can just plug in that...
The CELL function is used to get the full file name and path:
CELL("filename",A1)
The result looks like this:
path[workbook.xlsm]sheetname
CELL returns this result to the MID function as the text...
The 1st dimension
To get the first dimension, we are using this formula in C4:
=LEFT(B4,FIND("x",B4)-1)
This works by extracting text starting at the LEFT. The number of characters is calculated by...
At the core, this formula builds a level 1 and level 2 number and concatenates the two numbers together with a period (".") as a separator. The result is a value like "1.1". The "level 1" number is generated with...
Assuming you have a full name in column B, a first name in column C, and a last name in column D, you can use a formula that looks like this:
=TRIM(MID(B5,LEN(C5)+1,LEN(B5)-LEN(C5&D5)))
At the core, the MID...
This formula works for times entered in a particular format as shown below:
00h01m13s
00h01m08s
08h02m59s
Note the text string is always 9 characters long, and each component is 2 digits.
The core of this formula...
At the core, this formula uses an array operation to generate an array of letters from the input text, translates each letter individually to a number, then joins all numbers together again and returns the output as a...
The named range "sheetnames" is created with this code:
=GET.WORKBOOK(1)&T(NOW())
GET.WORKBOOK is a macro command that retrieves an array of sheet names in the current workbook. The resulting array looks like...
The core of this formula is the DATE function, which is used to assemble a proper Excel date value. The DATE function requires valid year, month, and day values, so these are parsed out of the original text string as...
Sometimes you encounter data that mixes units directly with numbers (i.e. 8km, 12v, 7.5hrs). Unfortunately, Excel will treat the numbers in this format as text, and you won't be able to perform math operations on such...
Working from the inside out, the LEN function calculates the length of the string, and this is joined by concatenation to "1:", creating a text range like this: "1:3"
This text is passed into INDIRECT, which evaluates...
Excel doesn't have a way to cast the letters in a text string to an array directly in a formula. As a workaround, this formula uses the MID function, with help from the ROW and INDIRECT functions to achieve the same...
At the core, this formula looks for a line delimiter ("delim") and replaces it with a large number of spaces using the SUBSTITUTE and REPT functions.
Note: In older versions of Excel on a Mac, use CHAR(13) instead of...