Hi all,

I have a spreadsheet containing 65,000 rows. Each row has a cell containing
a string in the following format:

Data\Docs\20011018\kaateec\20114.Tif

What I need to be able to do is extract the last characters from the right
of the string until we reach the last '\' character. For example, the above
would result in:

20114.Tif

So yes, I'm trying to extract a filename only here. The filename vary in
length, some 4 characters and some up to 8 characters so using this:

Right(A1,9)

....simply does not work on all occasions.

Should be straight forward but I simply cannot get it to work.

Many Thanks in advance.

This UDF does the job:
Function fname(inpstr)
fname = Mid(inpstr, InStrRev(inpstr, "\") + 1)
End Function
Regards,
Stefi

€žashg657€� ezt Ã*rta:

Hi all,

I have a spreadsheet containing 65,000 rows. Each row has a cell containing
a string in the following format:

Data\Docs\20011018\kaateec\20114.Tif

What I need to be able to do is extract the last characters from the right
of the string until we reach the last '\' character. For example, the above
would result in:

20114.Tif

So yes, I'm trying to extract a filename only here. The filename vary in
length, some 4 characters and some up to 8 characters so using this:

Right(A1,9)

...simply does not work on all occasions.

Should be straight forward but I simply cannot get it to work.

Many Thanks in advance.

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

Vijay

"ashg657" wrote:

Hi all,

I have a spreadsheet containing 65,000 rows. Each row has a cell containing
a string in the following format:

Data\Docs\20011018\kaateec\20114.Tif

What I need to be able to do is extract the last characters from the right
of the string until we reach the last '\' character. For example, the above
would result in:

20114.Tif

So yes, I'm trying to extract a filename only here. The filename vary in
length, some 4 characters and some up to 8 characters so using this:

Right(A1,9)

...simply does not work on all occasions.

Should be straight forward but I simply cannot get it to work.

Many Thanks in advance.

With your full path in A1; try the below formula..

=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(CHAR(32),LEN(A1 ))),LEN(A1)))

If this post helps click Yes
---------------
Jacob Skaria


"ashg657" wrote:

Hi all,

I have a spreadsheet containing 65,000 rows. Each row has a cell containing
a string in the following format:

Data\Docs\20011018\kaateec\20114.Tif

What I need to be able to do is extract the last characters from the right
of the string until we reach the last '\' character. For example, the above
would result in:

20114.Tif

So yes, I'm trying to extract a filename only here. The filename vary in
length, some 4 characters and some up to 8 characters so using this:

Right(A1,9)

...simply does not work on all occasions.

Should be straight forward but I simply cannot get it to work.

Many Thanks in advance.

You could also do an editreplace...
Find what: *\
Replace with: (blank)

Do this on a copy of the column if you want to keep the original values.

"ashg657" wrote:

Hi all,

I have a spreadsheet containing 65,000 rows. Each row has a cell containing
a string in the following format:

Data\Docs\20011018\kaateec\20114.Tif

What I need to be able to do is extract the last characters from the right
of the string until we reach the last '\' character. For example, the above
would result in:

20114.Tif

So yes, I'm trying to extract a filename only here. The filename vary in
length, some 4 characters and some up to 8 characters so using this:

Right(A1,9)

...simply does not work on all occasions.

Should be straight forward but I simply cannot get it to work.

Many Thanks in advance.

Many thanks for your assistance guys. Solutions have worked very well.

Cheers.

"Jacob Skaria" wrote:

With your full path in A1; try the below formula..

=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(CHAR(32),LEN(A1 ))),LEN(A1)))

If this post helps click Yes
---------------
Jacob Skaria


"ashg657" wrote:

Hi all,

I have a spreadsheet containing 65,000 rows. Each row has a cell containing
a string in the following format:

Data\Docs\20011018\kaateec\20114.Tif

What I need to be able to do is extract the last characters from the right
of the string until we reach the last '\' character. For example, the above
would result in:

20114.Tif

So yes, I'm trying to extract a filename only here. The filename vary in
length, some 4 characters and some up to 8 characters so using this:

Right(A1,9)

...simply does not work on all occasions.

Should be straight forward but I simply cannot get it to work.

Many Thanks in advance.