Split text string at specific character
The first formula uses the FIND function to locate the underscore(_) in the text, then we subtract 1 to move back to the "character before the special character":
FIND("_",B5)-1 // returns 6
In this example , FIND returns 7, so we end up with 6. This result is fed into the LEFT function as "num_chars" – the number of characters to extract from B5, starting from the left:
The result is the string "011016". To get the second part of the text, we use FIND with the RIGHT function.
We again use FIND to locate the underscore (_) at position 7, then subtract this result from the total length of the text in B5 (22), calculated with the LEN function:
This gives us 15 (22-7), which is fed into the RIGHT function as "num_chars" – the number of characters to extract from B5, starting from the right:
The result is the string "Assessment.xlsx"