Cleaning not delimited data columns

[ekurdziel]

Hi everyone, I've inherited some data that isn't arranged in a helpful way and can use the group's help. I have data in a column that I need to separate, but the data does not follow a pattern that is easy to correct via delimited text to columns or LEFT/RIGHT.

The value within the cell is made of what should be separated into 2 columns. I do have one (1) of the values already in a separate column, but need to isolate the other portion to use it effectively.

For example, my data is formatted where:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[/TR]
[TR]
[TD]A AAA[/TD]
[TD]A AAA BBB-BB[/TD]
[/TR]
[TR]
[TD]AAA AA[/TD]
[TD]AAA AA BBB BB B[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD]AAA BB-B-BBBB[/TD]
[/TR]
[TR]
[TD]AA AAA AAA[/TD]
[TD]AA AAA AAA BB B[/TD]
[/TR]
</tbody>[/TABLE]









What I need to do is separate the B values into their own column. I've been trying to determine a function that can determine if Column 2 includes the value from Column 1, it would search/replace the value in Column 1 with no text.

Any thoughts on how to accomplish this? Thanks in advance for your help!

Eric

 

[Jonmo1]

Try

=SUBSTITUTE(B2,A2,"")

You may need to add TRIM if you don't want the leading space

=TRIM(SUBSTITUTE(B2,A2,""))

 

[ekurdziel]

Hi Jonmo1, that did the trick. I still have some work to do on cleaning this data, but I believe that put me down the right path. I'll make sure to reply back to this if I get stuck again.

Thanks for the quick response and help!

Eric

 

[Jonmo1]

You're welcome.