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

Attached Files

Extract Text from string.xlsx (9.1 KB, 5 views)

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

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).

Attached Files

Extract Text from string.xlsx (9.6 KB, 4 views)

Thank you for looking into this matter for me. I realised it wasn't enough data, I've now attached a new sheet with more information. I hope you can help.

Many thanks,

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,

I upload an updated sheet with more information

Attached Files

Extract Text from string updated.xlsx (9.8 KB, 2 views)

To whose post does this belong to? In case to my post #4, then the formula returns a string of any length, situated between code string, and 1st occurrence of any numeric character after the code string.

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.

See attached

Attached Files

Extract Text from string updated.xlsx (11.3 KB, 6 views)

__________________
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

Thank you so much for your help. The formula works like a charm.

Have a wonderful day.

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)

__________________
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