Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-03-2018, 07:16 AM
Haha88 Haha88 is offline Help with Formula to extracting text from text string Windows 8 Help with Formula to extracting text from text string Office 2010 32bit
Advanced Beginner
Help with Formula to extracting text from text string
 
Join Date: Mar 2015
Posts: 75
Haha88 is on a distinguished road
Default Help with Formula to extracting text from text string

Hi,

I hope someone can please help to create a formula to extract text from a text string.



Attached an example sheet.

Thank you.
Attached Files
File Type: xlsx Test.xlsx (9.0 KB, 14 views)
Reply With Quote
  #2  
Old 02-03-2018, 09:21 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Help with Formula to extracting text from text string Windows 7 64bit Help with Formula to extracting text from text 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

Data - Text to columns - set : as delimiter - do not import unnecessary columns in the wizard's last window
__________________
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 02-03-2018, 09:29 AM
NoSparks NoSparks is offline Help with Formula to extracting text from text string Windows 7 64bit Help with Formula to extracting text from text string Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

does this work?

=MID(C3,FIND("::",C3,1)+2,LEN(C3)-FIND("::",C3,1)-2)
Reply With Quote
  #4  
Old 02-03-2018, 11:11 AM
Haha88 Haha88 is offline Help with Formula to extracting text from text string Windows 8 Help with Formula to extracting text from text string Office 2010 32bit
Advanced Beginner
Help with Formula to extracting text from text string
 
Join Date: Mar 2015
Posts: 75
Haha88 is on a distinguished road
Default

Hi,

Thank you for looking into this.

I tried and the result comes back with #Value!

=MID(N29,FIND("::",N29,1)+2,LEN(N29-FIND("::",N29,1)-2))


What has I've done wrong?

Thanks,
Reply With Quote
  #5  
Old 02-03-2018, 12:47 PM
jeffreybrown jeffreybrown is offline Help with Formula to extracting text from text string Windows Vista Help with Formula to extracting text from text string Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

I might have used a sledge hammer...

D3 copied down to D5

=MID(C3,FIND("::",C3,1)+2,LEN(MID(C3,FIND("::",C3, 1)+2,LEN(C3)))-LEN(MID(C3,FIND("@",SUBSTITUTE(C3,":","@",4)),LEN( C3))))
Reply With Quote
  #6  
Old 02-03-2018, 12:47 PM
NoSparks NoSparks is offline Help with Formula to extracting text from text string Windows 7 64bit Help with Formula to extracting text from text string Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Did you check to see if you entered your formula as per the suggestion ?
Reply With Quote
  #7  
Old 02-03-2018, 02:52 PM
NoSparks NoSparks is offline Help with Formula to extracting text from text string Windows 7 64bit Help with Formula to extracting text from text string Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

removed.

As my suggestion only satisfied the result you show.
I suspect Jeffrey is a better mind reader for the results you don't show.
Reply With Quote
  #8  
Old 02-04-2018, 04:42 PM
Haha88 Haha88 is offline Help with Formula to extracting text from text string Windows 8 Help with Formula to extracting text from text string Office 2010 32bit
Advanced Beginner
Help with Formula to extracting text from text string
 
Join Date: Mar 2015
Posts: 75
Haha88 is on a distinguished road
Default

Quote:
Originally Posted by jeffreybrown View Post
I might have used a sledge hammer...

D3 copied down to D5

=MID(C3,FIND("::",C3,1)+2,LEN(MID(C3,FIND("::",C3, 1)+2,LEN(C3)))-LEN(MID(C3,FIND("@",SUBSTITUTE(C3,":","@",4)),LEN( C3))))
Hi,

This works wonderfully!

Thank you,
Reply With Quote
  #9  
Old 02-05-2018, 12:57 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Help with Formula to extracting text from text string Windows 7 64bit Help with Formula to extracting text from text 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

Did you, by accident, pay the slightest attention to post #2?
__________________
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
  #10  
Old 02-05-2018, 01:04 AM
Haha88 Haha88 is offline Help with Formula to extracting text from text string Windows 8 Help with Formula to extracting text from text string Office 2010 32bit
Advanced Beginner
Help with Formula to extracting text from text string
 
Join Date: Mar 2015
Posts: 75
Haha88 is on a distinguished road
Default

Quote:
Originally Posted by Pecoflyer View Post
Did you, by accident, pay the slightest attention to post #2?
Oh, apologies! I didn't understand what you tried to tell. U will also try your method, will let you know if it works. Thank you.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula to Extract text from a text string Haha88 Excel 2 11-14-2017 01:32 AM
Help with Formula to extracting text from text string Extract text from a text string Haha88 Excel 8 02-13-2017 05:06 PM
Help with Formula 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 with Formula 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
Help with Formula to extracting text from text string Extracting text from a Word Doc into Excel dgcarlin Word VBA 1 07-06-2012 05:46 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:58 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