Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-23-2022, 09:15 AM
Haha88 Haha88 is offline Extract specific text from a text string Windows 8 Extract specific text from a text string Office 2010 32bit
Advanced Beginner
Extract specific text from a text string
 
Join Date: Mar 2015
Posts: 75
Haha88 is on a distinguished road
Default Extract specific text from a text string

Hi community,

Please can someone help extract specific text from a long text string.

I attached the data and what is required to extract.



Many thanks for you support.

Haha
Attached Files
File Type: xlsx Book4.xlsx (9.0 KB, 12 views)

Last edited by Haha88; 09-23-2022 at 09:19 AM. Reason: No attached file
Reply With Quote
  #2  
Old 09-23-2022, 09:31 AM
p45cal's Avatar
p45cal p45cal is offline Extract specific text from a text string Windows 10 Extract specific text from a text string Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Nothing attached
Reply With Quote
  #3  
Old 09-23-2022, 11:33 AM
Logit Logit is offline Extract specific text from a text string Windows 10 Extract specific text from a text string Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

https://excelmacromastery.com/vba-left-right-mid/
Reply With Quote
  #4  
Old 09-23-2022, 05:03 PM
Haha88 Haha88 is offline Extract specific text from a text string Windows 8 Extract specific text from a text string Office 2010 32bit
Advanced Beginner
Extract specific text from a text string
 
Join Date: Mar 2015
Posts: 75
Haha88 is on a distinguished road
Default

Quote:
Originally Posted by Haha88 View Post
Hi community,

Please can someone help extract specific text from a long text string.

I attached the data and what is required to extract.

Many thanks for you support.

Haha
Just to be clear, I'm looking for an excel formula.
Reply With Quote
  #5  
Old 09-23-2022, 06:03 PM
Logit Logit is offline Extract specific text from a text string Windows 10 Extract specific text from a text string Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

Code:
      Formula in D4 :    =MID(C4,43,5)       Formula in D5 :    =MID(C5,43,5)       
  
      Formula in E4 :    =RIGHT(C4,29)       Formula in E5 :    =RIGHT(C5,29)
Reply With Quote
  #6  
Old 09-24-2022, 04:57 AM
Haha88 Haha88 is offline Extract specific text from a text string Windows 8 Extract specific text from a text string Office 2010 32bit
Advanced Beginner
Extract specific text from a text string
 
Join Date: Mar 2015
Posts: 75
Haha88 is on a distinguished road
Default

Quote:
Originally Posted by Logit View Post
Thank you for your help. I can't use vba on my current workbook.
Reply With Quote
  #7  
Old 09-24-2022, 05:01 AM
Haha88 Haha88 is offline Extract specific text from a text string Windows 8 Extract specific text from a text string Office 2010 32bit
Advanced Beginner
Extract specific text from a text string
 
Join Date: Mar 2015
Posts: 75
Haha88 is on a distinguished road
Default

Quote:
Originally Posted by Logit View Post
Code:
      Formula in D4 :    =MID(C4,43,5)       Formula in D5 :    =MID(C5,43,5)       
  
      Formula in E4 :    =RIGHT(C4,29)       Formula in E5 :    =RIGHT(C5,29)
Thank you for your help.

This formula will work for this particular line. However, there are many other lines that don't have the same length of characters therefore these don't quite work.

Many thanks,
Reply With Quote
  #8  
Old 09-24-2022, 06:04 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Extract specific text from a text string Windows 10 Extract specific text from a text string Office 2021
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

Then please provide more than one example of data that should be analyzed. thanks
__________________
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
  #9  
Old 09-26-2022, 12:05 AM
ArviLaanemets ArviLaanemets is offline Extract specific text from a text string Windows 8 Extract specific text from a text string Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Assumed the rule you need to use is always to extract a string having at least from one side enclosed with " - ", attached is an example to extract up to 4 such parts of original string. I used the character "|" to replace "-" when searching for specific occurrence of "-" in text. In case "|" is used in your data, you have to use some other unused replacement there.

You can simplify some of those formulas - but I did make them as uniform as possible. So when you want e.g. to expand formulas for 5 parts returned, simple copy-pasting of some existing formula, and some edit of it, will do.

In case the splitting your data is once-only task, you can instead of formulas use Text To Columns feature of excel. As start you have to replace all " - " with some single-character unused string (e.g. with "|"). Then you select the column with your strings (NB! You must have enough empty columns right of it for splited data), select from data menu Text To Columns, select Delimited option, set your split identifier (e.g. "|") as delimiter, and then click on Finish. Instead of single column of data you have now several different ones!
Attached Files
File Type: xlsx ExtractStringParts.xlsx (9.6 KB, 4 views)
Reply With Quote
  #10  
Old 09-27-2022, 04:20 PM
Steve Kunkel Steve Kunkel is offline Extract specific text from a text string Windows 10 Extract specific text from a text string Office 2019
Advanced Beginner
 
Join Date: May 2019
Location: Seattle area
Posts: 77
Steve Kunkel is on a distinguished road
Default

Another possibility:
If your original text is in C4, then

In D4, put
=MID(C4,SEARCH("Insurance:",C4)+10,5)

and in E4, put
=MID(C4,SEARCH("Insurance:",C4)+18,999)

As another user indicated, we need more examples of the original text, so we can detect patterns... For example, does it always contain "Insurance:" followed by a 5 digit number?
Reply With Quote
  #11  
Old 09-28-2022, 11:37 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Extract specific text from a text string Windows 10 Extract specific text from a text string Office 2021
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

@Haha please read post #8 and the last sentence of post #10 so we don't keep wasting time. Thx
__________________
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
  #12  
Old 11-01-2022, 05:58 AM
mrgramm mrgramm is offline Extract specific text from a text string Windows 10 Extract specific text from a text string Office 2021
Novice
 
Join Date: Jan 2020
Posts: 7
mrgramm is on a distinguished road
Default

Try using flash fill. It works well.
Reply With Quote
  #13  
Old 11-01-2022, 10:47 AM
kilroyscarnival kilroyscarnival is offline Extract specific text from a text string Windows 10 Extract specific text from a text string Office 2021
Expert
 
Join Date: May 2019
Posts: 344
kilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nice
Default

I see it says you have Excel 2010, and not one of the latest versions where you might be able to use PowerQuery or some of the new text functions rolling out.

This was helpful for me doing a similar thing. And I learn well by watching videos like this: https://www.youtube.com/watch?v=zpmnBuR7uXs

Best,

Ann
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract Text from a text string Haha88 Excel 3 05-19-2022 08:27 AM
Extract specific text from a text string Extract Bold text from string one4youman Word VBA 8 04-18-2019 12:31 AM
Formula to Extract text from a text string Haha88 Excel 2 11-14-2017 01:32 AM
Extract specific text from a text string Extract text from a text string Haha88 Excel 8 02-13-2017 05:06 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 06:53 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