|
#1
|
|||
|
|||
Extract specific text from a text string Help
Hi Community,
Please help with an excel formula to extract text from a text string in excel. I attached an example and what text needs extracting. Many thanks, Haha88 |
#2
|
||||
|
||||
You only provided ONE example. Will other strings have the same format?
__________________
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 |
#3
|
|||
|
|||
Quote:
Many thanks, |
#4
|
|||
|
|||
For given example, the string to extract the name from is
'Contractor Stephen McWhinnie 14 hours w/e 30-Sep-23' It looks like you want to get the name following some code string in source string, e.g. 'Contractor '. Now the conditions you haven't detailed: 1. Is there a single code string ('Contractor) indicating that the name will follow, or there may be several different ones? 2. Is the source string always starting with code string, or the code string may be placed anywhere in source string (like '<Some text here>, contractor Joe Smith ...')? 3. I will assume, that the name you want to extract always starts immediately after code string (otherwise it will be impossible to determine, from where the name starts), but how is determined, where it ends? Unless you have some other code string indicating the end of name (like ','), or you have the name always composed from single forename and single surname - i.e. the name always has a single space in it - there is no way to decide. From your example it looks like you have any numeric character ('1' in your example) in source string as second code string. I.e. the name string (probably with space in end) ends when any of numeric characters '0', '1', ..., '9' appears in source string. Assuming that you want to extract only contractors, placed anywhere in field, and having contractor name followed with space, and then with some number, in attached is an example how to do this (I used additional calculated columns for this formula to calculate the start position of name, and the name length - because having the formula for start position of name to be inserted 10 times to calculate the length of it would surely exceed the limit for formula length). |
#5
|
|||
|
|||
Thank you for looking into this matter for me. Your formula will work if the name is at the same length/ number of character but unfortunately they are not. I have attached a revised sheet with more information and hope you can help.
Many thanks, |
#6
|
|||
|
|||
Quote:
Sorry, but for next 2 weeks I can't see what you have in your example file - I'm on annual leave currently, and I don't have MS Office in my home computer. |
#7
|
|||
|
|||
I upload an updated sheet with more information
|
#8
|
||||
|
||||
See attached
__________________
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
|
|||
|
|||
Thank you so much for your help. The formula works like a charm.
Have a wonderful day. |
#10
|
||||
|
||||
You're welcome
For members unwilling or unable to open the sheet this is the formula I suggested (replace ; and \ by a comma if needed) Code:
=SUBSTITUTE(TRIM(LEFT(E5;MIN(FIND({0\1\2\3\4\5\6\7\8\9};E5&"0123456789")-1)));"Contractor ";"")
__________________
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 |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Extract specific text from a text string | Haha88 | Excel | 12 | 11-01-2022 10:47 AM |
Extract Text from a text string | Haha88 | Excel | 3 | 05-19-2022 08:27 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 |