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: 55
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, 7 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: 648
p45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud of
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: 453
Logit will become famous soon enoughLogit will become famous soon enough
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: 55
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: 453
Logit will become famous soon enoughLogit will become famous soon enough
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: 55
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: 55
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,601
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
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 online now Extract specific text from a text string Windows 8 Extract specific text from a text string Office 2016
Expert
 
Join Date: May 2017
Posts: 726
ArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to behold
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, 2 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: 70
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,601
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
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
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 12:54 AM.


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