Formula to remove contents of one cell from another cell

[LightingPop]

Hi, I am trying to remove the characters and text of one cell from another. This is because I am getting a lot of data that was suppose to be delimited but is not for some terrible reason. So all of my data is in cell B1, instead of a nice format. Luckily the only info in B1 that I need always starts with the letters "SCD". I know how to find the first occurrence of the text string which starts with SCD but now I need to remove that first occurrence and pull the next one so on and so forth.

Here is an example, where I have cells B1 and A2; and the formula would go in B2. So the contents of cell A2 are found and removed from B1.

B1: 111 444 SCD999 333 --- sss eee iii SCD444 ddd lll SDC888

A2: SCD999

B2: 111 444 333 --- sss eee iii SCD444 ddd lll SDC888

[Richard Buttrey]

Hi,

If the strings are of equal length 14 to the end of the SCD999 reference then just use TextToColumns with a Fixed width split at positions 9 & 14.
Then just delete the cokumn with the SCD reference. If necessary concatenate the two columns that are left to get back to a single string.

[LightingPop]

Oops, Sorry about that. The data really is in no format or pattern that makes sense, only that the data I need starts with SCD. Sometimes those data points are 4 characters away or 30 in the cell.

[Richard Buttrey]

Hi,

If the SCD references are always SCD??? (i.e. always 6 characters) then you could use a Find/Replace.
Select the range of cells and in the Find box enter SCD???, leave the Replace box blank and then OK

[LightingPop]

The are not. I would do find & replace but I have to do this many times as in the contents of cell B1 has 55 data points in it and this is just the first file.

It is frustrating because I basically just need a Find and Replace formula, like =FindAndReplace(start_text,remove_text).

Where the start_text is the cell which contains the original output and the remove_text is what is being deleted.

[Richard Buttrey]

Hi,

How about

Please Login or Register  to view this content.

[LightingPop]

I am sorry, what is that suppose to do?

[Richard Buttrey]

It will remove the first occurrence of the SCD??? string and leave the rest of the string.