How to remove specific text before a character

Hello everyone,

I'd like to ask for your help to achieve the following.

1) In any cell, I would like to remove "Area#xxxxxxxxx" (where x are random numbers).

Example:
"INFO Log - [sys] Area#541185471Character#46545"


2) I would like to remove x characters before a word.

Example:
2013-08-28.txt@INFO

I would need to remove 14 characters before the character "@".



I tried to play with the LEN and RIGHT/LEFT formula but so far, I can't get it to work...
The idea is to parse some text and remove the part in red (I was thinking about using SUBSTITUTE).

Thanks in advance for your help. Please let me know if my explanation isn't clear.

Formula:

Please Login or Register  to view this content.

will do number 2 from above.

Can you give more detail about 1. Is the data all the same length? Is it all the same layout, eg, does it all have "[sys] " before the part you want removed? etc.

I'm really sorry, I was not clear enough for both questions. I'll clarify.

In the Excel document, I have a lot of lines which include the following text:

"Random Text 2013-08-28.txt@Centre5 INFO - [sys] Character#1234Area#1234 Random Text"

In order to make these lines easier to read, I made a macro "Search & Replace" to remove the unnecessary bits. However, because the text in red is random (and the numbers 1234 are random as well and their length may vary), I don't know how to remove it automatically with my macro.

My macro for Search & Replace is:

Cells.Replace What:="Old_Text", Replacement:="New_Text", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

This is why I need a formula to:

1) remove x characters before the "@" (and without deleting more than x characters because I need what precedes).
2) Remove "Area + all numbers that follows" (and numbers only).

I hope this makes it easier to understand what I need.
Again, sorry for not being clear at first and thanks a lot for your help.

Can you please upload a sheet with a few examples of the data so we can look for specific patterns that we can use to determine the number of characters to remove? At least 20 would be good.

it would be easier for us if you could list the elements of the string that are known, with their properties
we could then create a process that places each of the elements into the 6 or so parts, and return only the parts you want

For example ---

"Random Text 2013-08-28.txt@Centre5 INFO - [sys] Character#1234Area#1234 Random Text"

1/ Random Text - this is always alpha with no numbers
2/ yyyy-mm-dd.txt - this is always in this format
3/ @Centre5 INFO - [sys] - this is always a Centre with a numer
4/ Character#1234 - variable in length but will always contain a #
5/ Area#1234 - variable in length but will always contain a #
6/ Random Text - this is always alpha with no numbers

It's a bit tricky to copy/paste the exact content of the line but it looks like this:

2014-05-05 06:51:42 ThisIsATest yyyy-mm-dd.txt@Centre5 INFO talk - [sys] Character#1234 Area#1234 LongText

ThisIsATest: contains letters and numbers
yyyy-mm-dd.txt: this is always this format and is followed by "@".
Centre5: The name changes and contains letters and numbers. It is only 1 word though.
INFO: This is always there
talk: It changes and can be replaced by another word (letters only and 1 word).
[sys]: it can be replaced but is always letters.
Character#1234: "Character#" is always there but "1234" is random numbers which length varies.
Area#1234: "Area#" is always there but "1234" is random numbers which length varies.
LongText: can be any sentence with letters and numbers.

In red are the part I need to remove.

I'll see if I can add a sheet example but it would contains the same kind of line as the one above.
Again, I thank you very much for your help and I hope my explanation is good enough.

Formula:

Please Login or Register  to view this content.

will get rid of the first red part (assuming text is in A1)

Formula:

Please Login or Register  to view this content.

It's complicated, but it works. Others may be able to refine it.

Formula:

Please Login or Register  to view this content.

A bit shorter

In case someone is checking this thread, I'm currently using this solution with VBA code:

Cells.Replace What:="Entity*,", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

This way, it deletes everything from "Entity" to the next "," character.

I did the same thing for the date:

Cells.Replace What:=" 20*txt", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

Thanks again for your help. I hope one day I can help you back.