Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract string after specific characters
I have a worksheet with the drive paths to thousands of documents.
I would like to copy 2 sets of numbers (as string) to adjacent cells. Example: A1 contains "D:\OtherFiles\CLOSED\K\12-345 Smith, John\001 Estate Planning\Correspondence\Memo.doc" I need to extract the 6 characters after the 4th backslash ("12-345") to B1, and the 3 characters after the 5th backslash ("001") to C1. These numbers all fall after the 4th and 5th backslashes, but at varying character lengths and the numbers are variable, between 01-001 and 99-999. How do I search for those backslashes, and then copy the data to the next cells? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract string after specific characters
Hi
=MID(SUBSTITUTE(A1,"\","^",4),FIND("^",SUBSTITUTE( A1,"\","^",4))+1,6) and =MID(SUBSTITUTE(A1,"\","^",5),FIND("^",SUBSTITUTE( A1,"\","^",5))+1,3) -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract string after specific characters
Cool, thanks! That's close....
The only problem is that I need the data in B1 and C1 to be the actual data from A1, and not the underlying formula itself. Is there vb that can do this? Sorry I didn't specify in the original post. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract string after specific characters
Copy these 2 cells and paste special as values. See Edit menu, then Paste
Special. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music " wrote: Cool, thanks! That's close.... The only problem is that I need the data in B1 and C1 to be the actual data from A1, and not the underlying formula itself. Is there vb that can do this? Sorry I didn't specify in the original post. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract string after specific characters
We can deal with that!
Thanks much for your help! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract string after specific characters
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract string after specific characters
On Wed, 11 Jul 2007 03:20:01 -0700, Wigi
wrote: Nice Ron Thanks c.Offset(0, 1).ClearContents c.Offset(0, 1).NumberFormat = "@" c.Offset(0, 2).ClearContents c.Offset(0, 2).NumberFormat = "@" could be done in 2 lines with a Resize. Good suggestion: c.Offset(0, 1).Resize(1, 2).ClearContents c.Offset(0, 1).Resize(1, 2).NumberFormat = "@" --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract characters from Right of string | Excel Worksheet Functions | |||
How to extract specific text from a string of characters | Excel Worksheet Functions | |||
Formula to extract a specific word from text string | Excel Worksheet Functions | |||
Extract specific value from a long text string | Excel Worksheet Functions | |||
Text String - Specific Characters | Excel Worksheet Functions |