Given your example strings above, do you want the final results to be
\\emea.com\folder_01\subfolder\many_files
\\emea.com\folder_02\subfolder2\something
\\emea.com\folder_03\sub
? If so, easier to use a defined name like seq referring to =ROW(INDEX($1:$1048576,1,1):INDEX($1:$1048576,255,1))-1 and cell formulas like
where X99 is a placeholder for the actual cells addresses containing such strings. ADDED: this is an array formula; hold down [Ctrl] and [Shift] keys before pressing [Enter].
The definition of seq is immune to row insertion/deletion. It evaluates to an array of sequential integers from 0 to 254. If your strings could be longer than 255 characters, increase the 255 in the definition of seq.
This is something both LibreOffice Calc and Google Sheets handle much better than Excel. Those others provide regular expression functions. In Google Sheets, this only takes
=REGEXREPLACE(X99,"\\*$","")
LinkBack URL
About LinkBacks
Register To Reply
Originally Posted by Adtxc
Bookmarks