Excel Query Lads

I need a couple of Excel queries but I’ll get to the second one later.

Here’s the first one:

I need to find the text in each cell of column A in the text of each cell in column B and remove it.

So say A1 is “Sausage Face Kent” and B1 is “Sausage Face KentKent is a county at the bottom of England lorem ipsum bollocks etc etc”

I would need A2 to become “Kent is a county at the bottom of England lorem ipsum bollocks etc etc”

Also note in B1 “KentKent” might make it harder.

Each row has a different term, so A2 and B2 might be “Sausage Face Lancashire”

Can anyone work out the query for that?

Thanks in advance (zero replies)

Substitute with an empty string as the replacement text is what you’re after. It might not work perfectly if you’ve got some odd cases but works fine for your examples here…

1 Like

Lovely stuff! Do you know how I’d do that across loads of rows at once? (Literally know nothing about this)

Assuming you’ve got all of your values in columns A and B like in my screenshot you can drag down the bottom right corner of C1 when it’s selected, or simply copy and paste that cell into the rest of column C. Either method will copy the formula but with cell references for the equivalent cells in the new rows.

Brilliant thank you.

1 Like

=MID(B1,LEN(A1)+1,LEN(B1))

Joe’s did the trick but thanks.

Don’t know if you’d know how to do this one, it’s more of a RegEx thing I think. Finding a string in the text and then removing everything from the beginning of that string to the end of all of the text.

e.g. find the text in bold and remove it plus everything that comes after it, to the end

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Integer euismod risus sit amet massa aliquet pharetra eget quis erat. Phasellus tempus, lorem vitae pretium vulputate, ligula lacus cursus urna, sed imperdiet risus sapien nec ligula. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Pellentesque vehicula lacinia nisl cursus porta.

Could bodge it with a custom split?

Ignore this, DID IT

Just did it :slight_smile: https://www.clarkle.com/notes/excel-remove-everything-after-a-character-like-a-question-mark-comma-or-underscore/

1 Like