Cocktail of Formulas needed to Trim Text. Large text in cell

I have a massive amount of data in each cell. The USEFUL INFORMATION that I need is actually VERY SMALL. However, at the moment i have to read the whole cell to extract what i do need... and there are 100s of rows of this.

Is there a formula (or perhaps in my case, sets of formulas), that can clean out my text to what I am looking for?

I have attached an example of what I have, with what I need.
Column A is what I have, and there are SOME EXAMPLES in Column B of what the resulting text should look like

I would suggest using Data - Text To Columns.
Select Delimited - Next
Put * in the 'Other' Box and click Finish.
That will then be split up into columns, each column is what was between each *.
Just delete the unneeded columns.

Originally Posted by CK76

If using formula.

Try...
=TRIM(MID(SUBSTITUTE(A2,"*",REPT(" ", 100)),200,100))

EDIT: Disregard this one. I missed bunch that won't be captured by this.
Ex: Row 777 & below.

CK, i am thinking that there 3 or 4 columns will be needed to achieve what i am looking for. Obviously, if all the text had the exact same pre-fix, then one single formula will take care of it. Since this is not the case, Im ok with having for example your formula being column b, and column C being a different derivative that for example captures the text after the word "Additional Description" being the key indicator.

I knew it was going to be hard and out of my scope. Do you think with multiple columns it will be easier?

You can try in different ways, but the convention of writing text: '* some text) * XXXX *' is not kept everywhere. Some texts lack appropriate "stars".

=TRIM(MID(A2,FIND("")"",A2,1)+2,FIND(""*"",A2,FIND("")"",A2,1)+2)-FIND("")"",A2,1)-2))

Originally Posted by Glenn Kennedy

...or

=TRIM(MID(SUBSTITUTE("*"&$A2,"*",REPT(" ",125)),375,125))

Glen... for the first 278 rows, this works perfect.
The next couple of dozen rows have no useful text, so that's why i wrote what i did in their B column. If there is no formula that can have written what I have, then i'll leave it saying "Crew Size: 1", and add a 2nd layer formula afterwards removing "Crew Size: 1".

From rows 321, the naming structure varies, making the formula you created unable to give me the desired result. Do you think you can add an IF function that states that if the words "Additional Description:" are there, then take the text that I want (as seen on B321, B322, B323)? That way, if "Additional Description:" is not there, then the formula you made can come into play.

Thanks Again!

Originally Posted by Jonmo1

I would suggest using Data - Text To Columns.
Select Delimited - Next
Put * in the 'Other' Box and click Finish.
That will then be split up into columns, each column is what was between each *.
Just delete the unneeded columns.

That would solve what i am looking for in MANY of the rows, but i'd say only 60% of them.

If i still have to weed out manually the "BAD DATA" even once its in a desired column, then i am still doing 1 by 1 review, which is what I hope not to do.

Do you have (or can you create) a list of the key phrases you're looking for in the strings ?

Originally Posted by Jonmo1

Do you have (or can you create) a list of the key phrases you're looking for in the strings ?

This is actually a little hard to do. That's why I put example of what i want in column B.

To give you a rough example though...

- For the 1st 270 examples, Post #3 has the right formula.
- Rows 279-320 have no useful info, and that's why i put that in column B.
- The rows that have the phrase "Additional Description:" need the text after this phrase pops up, if it's there at all, and is needed until the phrase "Crew Size" appears. In Rows 300s this appears.
- Rows 717 to 720 have no useful data for me to extract.
- Rows in the late 700s... After the phrase "(CMSPIWSUSER)" comes up, we have a name, a date, and time. AFTER THAT, the text is useful until the word "Switching Center" comes up.

I think those 5 criteria can cover like 90-95% of what i am looking for.

Originally Posted by Glenn Kennedy

That's why we want 10-20 rows of representative data, not 800. You get a bit distracted (bored) after the first few screens worth.

I'll take another look in the morning.

You make a great point Glenn. We see that the first 270 rows are of the same thing, so that would only solve a portion of my issue.
Since i don't get to choose what gets displayed in Column A, i had thought to just put it all up.
Sorry for making it harder than it should, and thanks for your help. :-)

Originally Posted by Jonmo1

Do you have (or can you create) a list of the key phrases you're looking for in the strings ?

Jonmo, did you get anything?

Originally Posted by Zer0Cool

Where does the data come from? I assume its an export from some other system? If so what system are you exporting from and have you looked to see if it has any ability to trim down the information pre-export to Excel?

Sometimes if pulled from a database or other content system it may be possible to query or limit the output such that you dont get as much stuff you dont need.

From SAP. They have given us VERY VERY LITTLE training on its full capabilities, and management doesn't see it as a priority that we learn its full use. I was lucky I even figured out how to extract this information.

Originally Posted by JPWRana

This is actually a little hard to do. That's why I put example of what i want in column B.

To give you a rough example though...

- For the 1st 270 examples, Post #3 has the right formula.
- Rows 279-320 have no useful info, and that's why i put that in column B.
- The rows that have the phrase "Additional Description:" need the text after this phrase pops up, if it's there at all, and is needed until the phrase "Crew Size" appears. In Rows 300s this appears.
- Rows 717 to 720 have no useful data for me to extract.
- Rows in the late 700s... After the phrase "(CMSPIWSUSER)" comes up, we have a name, a date, and time. AFTER THAT, the text is useful until the word "Switching Center" comes up.

I think those 5 criteria can cover like 90-95% of what i am looking for.

Does this help Jonmo1?

Sorry, I've kinda walked away from this thread.
When you said you didn't have or can't create a list of key phrases, my idea was eliminated from possibility.