Solved How can I extract a word from a sentence in Excel?

Microsoft Outlook 2013 32/64-bit (mail m...
October 4, 2018 at 08:35:53
Specs: Windows 64
I have information that comes back from an outside query like this: TE207: A change in the Customer's Country field (Out of Country).TE207: A change in the Customer's Country field (Out of Country).TE220B: Customer has added a new Product/Service.TE220B: Customer has added a new Product/Service.TE220B: Customer has added a new Product/Service.

I need to extract all of the codes, which is the TE numbers, and before you ask, no, not all codes start with TE. Also, they're different lengths from 3 to 6 characters right now. (This is subject to change without notice.

Currently I use a LEFT to collect the first code. = LEFT(I2,(FIND(":",I2,1)-1)) This worked fine until they started stringing them all into the same cell.

How can I separate them? I was thinking if it can't be done in one cell, maybe I'd extract the first code, then find the next : (colon) and take everything before that up to a space. (There's always a space between the description of one code and the beginning of the next code.

Any ideas?


See More: How can I extract a word from a sentence in Excel?

Reply ↓  Report •

#1
October 4, 2018 at 10:31:53
✔ Best Answer
Based on your example, where a Period proceeds each code and a Colon follows, the easiest approach would be to use the Text-To-Column feature, delimited by the period (.).

This should split the string into separate cells, each containing a string that starts with a code. Once that is done, your current formula can be used to extract the code from each cell since each cell now looks like the "old" ones where "This worked fine until they started stringing them all into the same cell."

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
October 4, 2018 at 11:29:23
EXCELLENT response, DerbyDad! That's perfect, thank you!

Reply ↓  Report •

#3
October 5, 2018 at 08:34:11
Okay, a new issue popped up after using the text-to-column - I ended up with one account which had 36 codes but only two were different, the others just repeated the same two.

What's your advice on getting rid of those duplicates so I don't end up with data stretching to column BF?


Reply ↓  Report •

Related Solutions

#4
October 5, 2018 at 09:46:48
Let's start with the root cause of the issue. There are some of us that are of the mind that Excel was never designed to be used for the myriad of text-based operations that many people use it for. Therefore (we feel) there is no way that you are going to find built in functions and features to deal with every single possible configuration of text data in a way that every single different user wants to.

Sure, Excel offers a number of "text manipulation" functions, but they can't handle every situation.

Now let's look at what you are trying to do:

You don't know how long the original text strings are going to be.
You don't know how many pieces of data you are going to need to extract.
You don't know if there will be duplicates of the data that you are looking to extract.
You don't know how many duplicates there might be.
You (probably) don't know where the duplicates are within the string.

It would take someone with much more Excel knowledge than I to build a formula that can handle all of those unknowns. I've seen people construct some very elegant formulas that do some amazing things. However, I'd be surprised if there is a single, simple text manipulation formula that can cleanly and neatly extract the data you want because of all of the unknowns.

I imagine that a macro could be written to deal with the unknowns, but I would need more details as to your overall process, where the output needs to be placed, etc. before I could even begin to write any code.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#5
October 5, 2018 at 10:48:12
I understand, we use Excel like a database instead of using Access. Excel is much easier for most users who don't know applications to use. I wouldn't expect it to have a single function for something like this. I'm looking for steps to end up with what I need.

And we've transposed the data into columns vs rows and use Data Tools - Remove Duplicates which works okay but only for a single column. I have 4800+ columns so not a very good work around.


Reply ↓  Report •

Ask Question