Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-05-2014, 01:39 AM
projectpupil7 projectpupil7 is offline Text in string Windows 7 64bit Text in string Office 2010 64bit
Novice
Text in string
 
Join Date: Nov 2014
Posts: 5
projectpupil7 is on a distinguished road
Unhappy 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.
Reply With Quote
  #2  
Old 11-05-2014, 12:46 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Text in string Windows 7 64bit Text in string Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #3  
Old 11-06-2014, 12:31 AM
projectpupil7 projectpupil7 is offline Text in string Windows 7 64bit Text in string Office 2010 64bit
Novice
Text in string
 
Join Date: Nov 2014
Posts: 5
projectpupil7 is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 11-06-2014, 11:25 PM
thetraininglady thetraininglady is offline Text in string Windows 7 64bit Text in string Office 2010 32bit
Competent Performer
 
Join Date: Sep 2014
Location: Sydney, Australia
Posts: 214
thetraininglady is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 11-07-2014, 01:30 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Text in string Windows 7 64bit Text in string Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

@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
Reply With Quote
  #6  
Old 11-21-2014, 10:11 PM
jolivanes jolivanes is offline Text in string Windows XP Text in string Office 2007
Advanced Beginner
 
Join Date: Sep 2011
Posts: 93
jolivanes will become famous soon enough
Default

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))))
Reply With Quote
  #7  
Old 11-22-2014, 08:07 PM
Bruno Campanini Bruno Campanini is offline Text in string Windows 8 Text in string Office 2013
Novice
 
Join Date: Nov 2014
Posts: 19
Bruno Campanini is on a distinguished road
Default

Quote:
Originally Posted by projectpupil7 View Post
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.
Try this UDF:

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

Bruno
Reply With Quote
  #8  
Old 11-23-2014, 12:03 AM
Catalin.B Catalin.B is offline Text in string Windows Vista Text in string Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

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
File Type: xlsm Text to Columns with formula and VBA.xlsm (179.0 KB, 8 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Text in string 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
Text in string Extracting a phone number from a string that contains text and numbers. hommi16 Excel 2 06-05-2013 09:19 PM
Text in string Find and replace a string of text errtu Word 1 01-31-2013 02:09 PM
Text in string 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

Other Forums: Access Forums

All times are GMT -7. The time now is 11:38 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft