#1
|
|||
|
|||
Text in string
Hi all, my first question as a newbie.
I have hundreds of text entries in a worksheet typically like this "Landlord work 110 / RJ*** / 2002 / RJL513 / 0 / A**** / AMSHDAM" and what i need to do is extract this "RJL513" piece of text from the string. I have tried using this =MID(R3,FIND("/",R3)+1,6) but can only get to the first slash. |
#2
|
||||
|
||||
Hi
the easy way is Data - Text to columns - use / as delimiter and select to not import the columns you don't need in the last step of the wizard
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
Hi Pecoflyer, thanks for the reply. Your answer was as expected and i did try this method but didn't get my answer i will have to try again. However, in my search i was offerred this rather long formula solution which works a treat so i would like to share this with the community.
=MID(SUBSTITUTE(SUBSTITUTE(E1,"/","",1),"/","",1),FIND("/",SUBSTITUTE(SUBSTITUTE(E1,"/","",1),"/","",1))+1,FIND("/",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E1,"/","",1),"/","",1),"/","",1))-FIND("/",SUBSTITUTE(SUBSTITUTE(E1,"/","",1),"/","",1))) Thanks for the help. Oh!! where is the thanks button |
#4
|
|||
|
|||
FYI for future reference,if you do not wish to affect other data in your workbook, copy & paste the text strings into a new blank workbook. Select the entire column containing this text string, choose Data > Text to Columns. Choose the File type as Delimiter then click Next, Change the type of delimiter to "space" (easiest option), then you can either just click Finish and your text string should be shown separated into column I, you can then cut & paste column I into another workbook for use.
This is 2 minute process at most to complete. |
#5
|
||||
|
||||
@thetraininglady - There is no need for using another workbook, copy pasting, etc...
In the last window of the Txt to column wizard, you can select the columns you do not wish to import. So there will only be one left which will replace the original without disturbing the rest
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#6
|
|||
|
|||
If it is always between the 3rd and 4th forward slashes, this does it also I think.
=TRIM(MID(A3,FIND(CHAR(1),SUBSTITUTE(A3,"/",CHAR(1),3))+1,FIND(CHAR(1),SUBSTITUTE(A3,"/",CHAR(1),4))-1-FIND(CHAR(1),SUBSTITUTE(A3,"/",CHAR(1),3)))) |
#7
|
|||
|
|||
Quote:
Public Function SS(S As String) As String SS = Trim(Split(S, "/")(3)) End Function Bruno |
#8
|
|||
|
|||
Hi,
Here is another option, using a formula: =TRIM(MID(SUBSTITUTE($A1,"/",REPT(" ",999)),(4-1)*999+1,999)) This will extract the 4'th item . If you want an item from another position, you can change the number 4 highighted in red to the position desired. You can even use the column parameter to automatically extract all elements from the text string: =TRIM(MID(SUBSTITUTE($A1,"/",REPT(" ",999)),(COLUMN(A1)-1)*999+1,999)) If you copy this to the right, it will be the equivalent of the text to columns functionality. I wrote a text to columns formula using another approach, but more complicated, at: http://www.myonlinetraininghub.com/t...-excel-formula But it's mostly an exercise for creating complex formulas, it's not very simple. In the attachment, you can find the file from the link provided, it also has a VBA version of splitting a text, similar to Bruno's solution. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Way to search for a string in text file, pull out everything until another string? | omahadivision | Excel Programming | 12 | 11-23-2013 12:10 PM |
Extracting a phone number from a string that contains text and numbers. | hommi16 | Excel | 2 | 06-05-2013 09:19 PM |
Find and replace a string of text | errtu | Word | 1 | 01-31-2013 02:09 PM |
Convert numbers to a specific text string | francis | Excel | 1 | 10-06-2011 01:43 PM |
Extract numbers from a text string | aleale97 | Excel | 4 | 02-10-2011 10:33 AM |