RETURN NAME: name is between -- and --

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,786
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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?
 
Upvote 0
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! :cool:
 
Last edited:
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,751
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top