Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract characters from Right of string
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract characters from Right of string
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract characters from Right of string
=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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract characters from Right of string
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract characters from Right of string
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract characters from Right of string
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert Leading Characters If String Is Only 7 Characters | Excel Discussion (Misc queries) | |||
How to extract specific text from a string of characters | Excel Worksheet Functions | |||
Extract Certain Characters and Numbers | Excel Worksheet Functions | |||
Extract the first few characters | Excel Worksheet Functions | |||
HOW DO I EXTRACT ALL CHARACTERS AFTER 5 CHARACTERS ON LEFT | Excel Worksheet Functions |