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:

Column 1	Column 2
A AAA	A AAA BBB-BB
AAA AA	AAA AA BBB BB B
AAA	AAA BB-B-BBBB
AA AAA AAA	AA AAA AAA BB B

<tbody>
</tbody>

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.