#1
|
|||
|
|||
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 Last edited by Haha88; 09-23-2022 at 09:19 AM. Reason: No attached file |
#2
|
||||
|
||||
Nothing attached
|
#3
|
|||
|
|||
|
#4
|
|||
|
|||
Just to be clear, I'm looking for an excel formula.
|
#5
|
|||
|
|||
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) |
#6
|
|||
|
|||
Quote:
|
#7
|
|||
|
|||
Quote:
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, |
#8
|
||||
|
||||
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 |
#9
|
|||
|
|||
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! |
#10
|
|||
|
|||
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? |
#11
|
||||
|
||||
@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 |
#12
|
|||
|
|||
Try using flash fill. It works well.
|
#13
|
|||
|
|||
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 |
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 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 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 |