Extracting text from cells

[malnahar]

Hello,

I am trying to find a way to extract text from a cell, attached is an example of what I have and what the required result is.

I want to remove all texts starting from the the word “Category” to “Comments “and again all text starting from the word “Customer “ to the word “Paid by”

Thanks in advance

[alansidman]

Try this formula

Please Login or Register  to view this content.

Alan

[malnahar]

Thank you Alan,

The number of characters from left is not always 25 characters , this may change , can we insert some other dynamic function other than Left (A2,25)

Regards

[alansidman]

Probably, I will have to look at it a bit later as I am not on my machine that has 2007 on it. It will entail a Left(A2, find(something)) formula.

[JBeaucaire]

In B2 and copied down:

=SUBSTITUTE(SUBSTITUTE(A2, LEFT(MID(A2, FIND("Category:",A2), LEN(A2)), FIND("Comments:", MID(A2, FIND("Category:", A2), LEN(A2)))-1), ""), LEFT(MID(A2,FIND("Customer:",A2),LEN(A2)), FIND("Paid By:",MID(A2,FIND("Customer:",A2),LEN(A2)))-1), "")


If you want to try and break that down I've left pieces of the formula in separate cells so you can see what they do. The formula A5 is the simple version of the formula above referring to the pieces of logic in the other colored cells. The mega-formula above is created by inserting the formulas from the colored cells in place of the cell references until the only reference left in the megaformula is the original cell a2.

[malnahar]

Many thanks Alan & JBeaucaire , appreciate your help , both were fantastic & generous

Best regards