Remove 3 letters from each text string

[BlackieHamel]

Friends,

I need this for a word puzzle I'm making. I have a list of 6-letter words that all contain SOU, and I want to strip out the SOU to find the remainder. But there's a twist. I need to think of these 6-letter words written underneath each other, so the SOU position in the 6-letter word may be written forward or backward, and even part at the beginning and part at the end of a word. In all cases, the SOU will appear, in that order, as the top half and the trigram I'm looking for is the remaining 3 letters. Thus, XES OUT can be written
SOU
EXT
reading clockwise, so the "correct answer" is EXT. Using a kind-of made-up word, AQUOSE, it could also be counterclockwise:
SOU
EAQ

. . . and the answer should read EAQ.

Is there a way I can do this with a formula that can then be copied? My examples don't always include SOU, and sometimes there are hundreds of possibilities, so I'm looking for a formula I can just "copy down."

Here are more examples of successful 3-letter transformations, given the 6-letter words in the first column.
[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]SOURON[/TD]
[TD="width: 64, bgcolor: transparent"]NOR[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SOUTER[/TD]
[TD="bgcolor: transparent"]RET[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OUSELS[/TD]
[TD="bgcolor: transparent"]LES[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SOUSED[/TD]
[TD="bgcolor: transparent"]DES[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]XESOUT[/TD]
[TD="bgcolor: transparent"]EXT
[/TD]
[/TR]
</tbody>[/TABLE]
AQUOSE EAQ

I don't even know if this is possible in Excel, but if someone knows how, I bet they're here in MrExcel. Thanks!

Blackie
[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[KolGuyXcel]

Let me check if I understood this problem correctly!

BSOULE should yield BEL.
DSOUCA should yield DAC.
EFUOSX should yield FEX.

Am I right?

 

[Eric W]

Try this:

Excel 2012

	A	B	C	D	E	F	G	H	I	J
SOURON	NOR	NOR	SOURON
SOUTER	RET	RET	ASOUBC
OUSELS	LES	LES	ABSOUC
SOUSED	DES	DES	ABCSOU
XESOUT	EXT	EXT	UABCSO
AQUOSE	EAQ	EAQ	OUABCS

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"]654[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]654[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]654[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]165[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]543[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]216[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"]654[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]321[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]216[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]432[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"]612[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]543[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C1[/TH]
[TD="align: left"]=IFERROR(MID("54321654",7-SEARCH("SOU",A1&A1),3),MID("45612345",SEARCH("UOS",A1&A1),3))[/TD]
[/TR]
[TR]
[TH]D1[/TH]
[TD="align: left"]=MID(A1,LEFT(C1),1)&MID(A1,MID(C1,2,1),1)&MID(A1,RIGHT(C1),1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



There are really only 12 cases to consider. 6 cases where you find SOU in the initial word starting at positions 1,2,3,4,5,6, and 6 more where it's reversed and you look for UOS instead. So I just looked at the different cases and determined which letters you want in each of those cases. The table I made in H1:J6 shows the results for the SOU cases. So on row 1, if SOU is found in the first position, the letters you want in order are the ones in positions 6,5,4. The other cases are the same. If SOU is not found then we look for UOS, and the table for UOS is the same as SOU except reversed. And as you can see, there's a definite pattern to the numbers.

So for SOU I made a "master" pattern of all the numbers. I search for SOU in the word (the word doubled, in case SOU wraps), find the position, and get the number pattern from the master pattern. If it's not found, I try with UOS. That's the formula in C1. Then the formula in D1 merely extracts the letters from the original word in the required order.

It's likely that this could be simplified further, either with formulas or VBA, but for now this should work. Maybe someone else following this thread can improve on it.

 

[XOR LX]

Perhaps, array formula**:

=TEXTJOIN(,1,IFERROR(MID(A1&A1,FIND(MID(CONCAT(MID(B$1,{1,2,3;3,2,1},1)),{1,4},3),A1&A1)+{5,3;4,4;3,5},1),""))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[XOR LX]

I should've mentioned that this formula goes in e.g. C1, with your first 6-letter word in A1 and your choice of 3-letter word (e.g. "SOU") in B1. Copy down to give similar results for words in A2, A3, etc.

Regards