Enhancing formula for extracting text from a single cell

**Mizuki** · 07-09-2012, 11:03 PM

Hi there! I've been testing and reading a lot of articles for excel, but it now comes to the point of desperation, and I am seeking your help to finally solve my problem. Please help!

Case: I need to extract words from a single cell and display them. With SEARCH, FIND, LEFT/MID/RIGHT functions, this should be easy, but it becomes trickier here.

Examples: These are the sample contents I'm working on;

In*Master Stock Transfer File, G***** C**** added Preview of “SI-1234”.pdf and 23 more files.
In*Master Stock Transfer File, D*** H***** P****** deleted the folder untitled folder.

(Names are replaced with asterisk for confidentiality)

^Those are exactly the contents of the cell. The challenges are; I need to extract the names, the action (e.g. added), and the file modified (located at the last part of each sentence). Problems are:

1. Extracting names: I used the MID function for this-

Formula:

=MID(B3,FIND(",",B3)+2,11)&"."

-but since each person has different number of words per name, I opted to choose the first 11 then finish it with '.', resulting to-

Formula:

G***** C*.

-this should be workable, but if there are better formulas out there, that'd be great!

2. Extracting the action: I came up with a formula using SEARCH (to find the word 'added'), then IF (if True, displays the word 'added'). I set the False argument to loop another IF(SEARCH formula for 'deleted', and then agin for other words. Only added was successful, and if it's SEARCHing for deleted, it shows an error. I need more help on this one.

3. Extracting the file modified: I used RIGHT for this one, but since not all files share the same number of characters, it will display only a fragment of the whole string I wanted to display. Is there any formula that will allow me to copy all the words after a set examples of words (after added and deleted).

That would be it. Thanks, any help will be appreciated. =)

**JBeaucaire** · 07-10-2012, 12:17 AM

TO keep it simple, you need to get the ADDED/DELETED first. Meanwhile, here are the three formulas:

A1: Your text
B1: =TRIM(LEFT(SUBSTITUTE(MID(A1, FIND(", ", A1)+2,1000), C1, REPT(" ", 1000)),1000))
C1: =IF(ISNUMBER(SEARCH("added", A1)), "added", "deleted")
D1: =TRIM(RIGHT(SUBSTITUTE(A1, C1, REPT(" ",1000)), 1000))

**Mizuki** · 07-10-2012, 12:46 AM

Thank you so much JBeaucaire! The load on my work has been cut off by a lot. I'm studying the formulas you have given to me, hopefully I'll manage to make my own when I need to add more to my workbook. Thanks again!