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.

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

__________________
Using O365 v2503 - 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

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

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.

@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

__________________
Using O365 v2503 - 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

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))))

Try this UDF:

Public Function SS(S As String) As String
SS = Trim(Split(S, "/")(3))
End Function

Bruno

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.

Attached Files

Text to Columns with formula and VBA.xlsm (179.0 KB, 10 views)