Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 02-13-2019, 03:40 AM
Haha88 Haha88 is offline Windows 8 Office 2010 32bit
Advanced Beginner
 
Join Date: Mar 2015
Posts: 43
Haha88 is on a distinguished road
Default Help to extracting text from text string

Hi,



Can someone please help me to extract text from a text string? I could never understand and be able to work out the formula.

Journal:JNL002668::S J Elder:SS-RWDG01:RW DigitalS-12708:::: extract: "S J Elder"

I attached an example sheet.

Many thanks,
Anh
Attached Files
File Type: xlsx Example.xlsx (13.5 KB, 2 views)
Reply With Quote
  #2  
Old 02-13-2019, 04:46 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 962
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default

What about

=MID(B3,FIND(":",B3,FIND(":",B3)+1)+2,FIND(":",B3; FIND(":",B3,FIND(":",B3)+1)+2)-1-FIND(":",B3,FIND(":",B3)+1)-1)
Reply With Quote
  #3  
Old 02-13-2019, 05:06 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 384
ArviLaanemets is on a distinguished road
Default

No easy universal formula exist. Or split the string, or have some luck with unique identifier in string.
Attached Files
File Type: xlsx PartFromString.xlsx (21.0 KB, 2 views)
Reply With Quote
  #4  
Old 02-13-2019, 05:47 AM
Haha88 Haha88 is offline Windows 8 Office 2010 32bit
Advanced Beginner
 
Join Date: Mar 2015
Posts: 43
Haha88 is on a distinguished road
Default

Quote:
Originally Posted by xor View Post
What about

=MID(B3,FIND(":",B3,FIND(":",B3)+1)+2,FIND(":",B3; FIND(":",B3,FIND(":",B3)+1)+2)-1-FIND(":",B3,FIND(":",B3)+1)-1)
Genius! Many thanks. Worked like a dream!

Thanks,
Haha
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with Formula to extracting text from text string Haha88 Excel 7 01-13-2019 01:33 AM
Help with Formula to extracting text from text string Haha88 Excel 9 02-05-2018 01:04 AM
Formula to Extract text from a text string Haha88 Excel 2 11-14-2017 01:32 AM
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


All times are GMT -7. The time now is 11:40 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft