Microsoft Office Forums Help to extracting text from text string

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-13-2019, 03:40 AM
Haha88 Haha88 is offline Help to extracting text from text string Windows 8 Help to extracting text from text string Office 2010 32bit
Advanced Beginner
Help to extracting text from text string
 
Join Date: Mar 2015
Posts: 46
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 Help to extracting text from text string Windows 10 Help to extracting text from text string Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,029
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
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 Help to extracting text from text string Windows 8 Help to extracting text from text string Office 2016
Expert
 
Join Date: May 2017
Posts: 463
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
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, 3 views)
Reply With Quote
  #4  
Old 02-13-2019, 05:47 AM
Haha88 Haha88 is offline Help to extracting text from text string Windows 8 Help to extracting text from text string Office 2010 32bit
Advanced Beginner
Help to extracting text from text string
 
Join Date: Mar 2015
Posts: 46
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 to extracting text from text string 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
Help to extracting text from text 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
Help to extracting text from text string 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 05:14 AM.


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