Text Extractions

[ysherriff]

How would i extract the regions from this path string without using text to column feature.

I am needing the formula to extract as: Region 1, Region 2, Region 3 or Region 4



S:\SPandA\Aegis\Clinical\DOR CJR Logs\Reports\Region 3\4063 - DOR CJR Tracking Log.xlsm
S:\SPandA\Aegis\Clinical\DOR CJR Logs\Reports\Region 1\4075 - DOR CJR Tracking Log.xlsm
S:\SPandA\Aegis\Clinical\DOR CJR Logs\Reports\Region 4\4063 - DOR CJR Tracking Log.xlsm
S:\SPandA\Aegis\Clinical\DOR CJR Logs\Reports\Region 2\4075 - DOR CJR Tracking Log.xlsm

Thank you

 

[Chihiro]

Since you have fixed path... assuming first string is in A1.
=MID(A1,47,8)

If path can vary...
=MID(A1,SEARCH("Region *",A1),8)

If "Region..." string can be more than 8 characters...
=MID(A1,SEARCH("Region *",A1),FIND("@",SUBSTITUTE(A1,"\","@",(LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))/LEN("\")))-SEARCH("Region *",A1))

 

[xlstime]

Hi ysherriff,

if the "S:\SPandA\Aegis\Clinical\DOR CJR Logs\Reports\" is static than you can refer below formula

=LEFT(RIGHT(A1,(LEN(A1)-46)),FIND("\",RIGHT(A1,(LEN(A1)-46)))-1)

How would i extract the regions from this path string without using text to column feature.

I am needing the formula to extract as: Region 1, Region 2, Region 3 or Region 4



S:\SPandA\Aegis\Clinical\DOR CJR Logs\Reports\Region 3\4063 - DOR CJR Tracking Log.xlsm
S:\SPandA\Aegis\Clinical\DOR CJR Logs\Reports\Region 1\4075 - DOR CJR Tracking Log.xlsm
S:\SPandA\Aegis\Clinical\DOR CJR Logs\Reports\Region 4\4063 - DOR CJR Tracking Log.xlsm
S:\SPandA\Aegis\Clinical\DOR CJR Logs\Reports\Region 2\4075 - DOR CJR Tracking Log.xlsm

Thank you