Extracting Strings

[ike92]

Hey Excel friends,

I cannot seem to figure out how to extract a string between two characters. I need to extract the Department, which is found in the section before gmail.com. I've been trying different combos of find(), len(), substitute(), but can't seem to get it correct!

Here are a couple of the strings:

THP80615/Computers/Spanish Publishing/gmail.com
THP80615/Desktops/Win7/Information Systems/gmail.com
THP80615/Foundation/President/gmail.com
THP80615/Computers/Operations/gmail.com
THP80615/Laptops/Win7/Computers/Information Systems/gmail.com
THP80615/Computers/Metering Stations/Shipping/Distribution/gmail.com

Thank you for your help in advance!

 

[flpnevs]

It will end up always in gmail.com? If that so, you can use that reference... "/gmail.com"

 

[Rick Rothstein]

Give this formula a try...

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",300)),600),300))

 

[GerryZ]

try:

Book1
	A
1	THP80615/Computers/Spanish Publishing/gmail.com
2	THP80615/Desktops/Win7/Information Systems/gmail.com
3	THP80615/Foundation/President/gmail.com
4	THP80615/Computers/Operations/gmail.com
5	THP80615/Laptops/Win7/Computers/Information Systems/gmail.com
6	THP80615/Computers/Metering Stations/Shipping/Distribution/gmail.com
7
8
9	Spanish Publishing
10	Information Systems
11	President
12	Operations
13	Information Systems
14	Distribution
Foglio3

Cell Formulas
Range	Formula
A9	=TRIM(LEFT(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",100)),200),100))

 

[ike92]

Thanks so much Mr. Rothstein! It works! I really appreciate your help and have a great day!!

 

[ike92]

Hey Mr. Rothstein, could you explain to me your function?
=TRIM(LEFT(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",300)),600),300))

I really want to understand this in order to emulate it in the future!

Thanks!!

 

[Rick Rothstein]

Hey Mr. Rothstein, could you explain to me your function?
=TRIM(LEFT(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",300)),600),300))

I really want to understand this in order to emulate it in the future!

The SUBSTITUTE function replaces all slashes with 300 blank spaces... this means that every item between slashes can be found within its own 300 character chunk of the substituted text surrounded by spaces. Now, there is some theoretical adjustment that needs to be make for the length of each item between the slashes, but if the number of spaces is sufficiently larger than any one of these individual lengths, then they overwhelm the need for any adjustments. So, since you wanted the second item from the left, we use the RIGHT function to grab 600 (2 times the number of spaces that was added) characters from the right... in there will be the next-to-last and last items (separated by 300 spaces). We use the LEFT function to grab the leftmost 300 characters from that 600 character chunk... it will only contain the next-to-last item surrounded by spaces... we use the TRIM function to get rid of them.

 

[ike92]

Thanks again! This definitely makes sense! You are such a boss!