Nth character in a string - parsing a shortcut

[schatham]

I have an Excel sheet with lots of shortcuts in the first column. It's the entire shortcut, including the .lnk extension.

What I need to do is to be able to parse out the string to make the shortcut human readable when I create the hyperlink. I'm saving the output as HTML for a user to open these shortcuts. It's on an internal LAN & secure. Using various versions of Excel (2010, 2007 & 2003, predominately the first).

Trying to make an ugly, 140+ character shortcut into a more readable form for web navigation purposes.

Up to a point, the shortcuts are similar:
\\a123\me\work\folder1\folder2\folder3\shortcut1.lnk
\\a123\me\work\folder1\folder4\folder5\shortcut1.lnk
\\a123\me\work\folder1\folder6\folder7\shortcut1.lnk
\\a123\me\work\folder1\folder8\folder9\shortcut1.lnk

but, later on, they vary from that point out.

\\a123\me\work\folder1\folder10\folder10A\folder11\shortcut1.lnk
\\a123\me\work\folder1\folder_12\folder12A\folder12B\shortcut1.lnk


The strings are the same up to the 7th occurrence of the "\" character, but after that, the foldernames start to change & the shortcut may be down a level (an extra folder) or more. Also, the lengths of the strings change as well, so I can only do positional parsing up to a point.

If possible, I would like to try to do this without doing VBA, but will if that's the only way it can be done.

I'd appreciate any guidance or input.
Thanks!

 

[Rick Rothstein]

Just SUBSTITUTE the common part away. For example, if this is in A1...

\\a123\me\work\folder1\folder2\folder3\shortcut1.lnk

and \\a123\me\work\folder1\ is common to all your links, then just use this formula...

=SUBSTITUTE(A1,"\\a123\me\work\folder1\","")

 

[schatham]

Thank you. I still haven't quite gotten it resolved yet, but am much further along.

The issues I'm dealing with now are due to different levels in the remaining folders, the fact that some of them have duplicate folder/sub-folder names (or partially duplicated), parsing beyond the 3rd or 4th remaining "\" and monitoring for numeric vs blank on some cells I'm doing a search on.

 

[schatham]

Thank you. I still haven't quite gotten it resolved yet, but am much further along.

The issues I'm dealing with now are due to different levels in the remaining folders, the fact that some of them have duplicate folder/sub-folder names (or partially duplicated), parsing beyond the 3rd or 4th remaining "\" and monitoring for numeric vs blank on some cells I'm doing a search on.

 

[Rick Rothstein]

Thank you. I still haven't quite gotten it resolved yet, but am much further along.

The issues I'm dealing with now are due to different levels in the remaining folders, the fact that some of them have duplicate folder/sub-folder names (or partially duplicated), parsing beyond the 3rd or 4th remaining "\" and monitoring for numeric vs blank on some cells I'm doing a search on.

Can you post several examples of the text you are dealing with (that shows off the above problem) and then show us what you want the formula to return for each of them? Doing that will be much better than trying to describe it in words (you know, a "picture" is worth a thousand words).