display unique customer formula

[macrorookie]

Hello -

I attached a simple spreadsheet with sample data to highlight my struggle.

I have many records for each customer, each record having a string of text (folder/file name) to identify documents related to that customer. I'm trying to simply identify each unique customer by showing the primary folder/file name for that customer (not the related folders/files). For example, I may have 100 records for Jane Doe, but I simply want to show the first folder/file name for "Jane Doe." Based on my data, I think I can do this by searching for folder/file names that end with "loan file/" and identify that record as a unique customer. My customer list is constantly changing, so I can't have a reference to "Jane Doe" in my formula. I just want to see one cell that I can identify Jane Doe as a unique customer.

Thanks so much for any help!

Jeff

[Vraag en antwoord]

It is not entirely clear to me what you mean. Maybe something like this. Otherwise, indicate the desired result in the file.

[Limor_OP]

Maybe something like that:

B5 = MID(C5,(SEARCH("Approved Loans",C5)+15),(SEARCH("loan file",C5))-(SEARCH("Approved Loans",C5)+15))

[macrorookie]

Those are both excellent solutions. In fact, they're more than I was hoping for. Thank you very much!!

[macrorookie]

Thank you, Vraag en antwoord! That is a great solution.

[macrorookie]

It appears I spoke too soon about the two great solutions I received. Those didn't work for other pieces of data.
In the new attached sample spreadsheet I showed my desire result, manually.
Thanks again for any help.

[AliGW]

Are you still using Excel 2010?

[macrorookie]

Actually, I'm using Excel 2016.

[AliGW]

Then please update your forum profile to Excel 2016. Thanks.

[Bo_Ry]

Please try at B5

=IF(RIGHT(C5,10)="loan file/",TRIM(SUBSTITUTE(LEFT(RIGHT(SUBSTITUTE(C5,"/",REPT(" ",50)),100),50),"loan file",)),"")

[AliGW]

You could us PowerQuery. Here's the M Code:

Please Login or Register  to view this content.

Excel 2016 (Windows) 32 bit

	C
26	Sample Data Set
27	All Files/1. David's loans with John/5. Approved Loans/Todd & Tina Smith loan file/
28	All Files/1. Joe's loans with MyHang/2. In Process Loans/Robert & Mary Levander loan file/
29	All Files/1. David's loans with John/5. Approved Loans/Alice & Frank Jones loan file/

Sheet: Sample data set