RETURN NAME: name is between -- and --

[itr674]

We have some file naming conventions where the persons LAST NAME and FIRST INITIAL is enclosed by "--", and then there is the form name then a period and the file application.

S:\### xx\xxx\### xxxxx xxxxx--### xx\LAST E\########--LAST E--FORMNAME.pdf

I have been trying to figure out how two get the persons LAST E working backwards from the "." just before file application namee

 

[Eric W]

Try:

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1,"--",REPT(" ",999)),1500),999))

 

[itr674]

Eric W--thank you!

That worked perfectly although it showed me I may have to rearrange some file names because some files had another item between the persons name and the form name, but this worked great...how did you figure it out?

 

[Eric W]

It helps that I've seen this kind of question before. In fact, I worked on 3 similar ones all in the last hour! The basic concept is clever (which I gleefully stole from some other post!): replace certain characters in the string with LOTS of spaces, then figure out where the data you want is, within a large range, pull it out using MID/LEFT/RIGHT, then TRIM off any extra spaces.

In this case I saw that after replacing the -- with 999 spaces, the string would look like: stuff (999 spaces) LAST E (999 spaces) FORMNAME.pdf. Taking 1500 characters from the right would get name and formname, as long as they were less than 500 bytes combined. Then taking 999 characters from the left of that gets the LAST E only, surrounded by lots of spaces, which TRIM eliminates.

Glad I could help!