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.

SOURON	NOR
SOUTER	RET
OUSELS	LES
SOUSED	DES
XESOUT	EXT

<tbody>
</tbody>

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

<tbody>
</tbody>

 

[Eric W]

I'm not entirely clear on your rules, but consider this:

	A	B	C	D	E	F	G	H
1	SOURON		SOU	RON	NOR	NOR
2	SOUTER		SOU	TER	RET	RET
3	OUSELS		SOU	SEL	LES	LES
4	SOUSED		SOU	SED	DES	DES
5	XESOUT		SOU	XET	TEX	EXT	?
6	AQUOSE		UOS	AQE	EQA	EAQ	?

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula C1 =IF(ISNUMBER(SEARCH("SOU",A1&A1)),"SOU","UOS") D1 =MID(SUBSTITUTE(A1&A1,C1,""),MAX(1,SEARCH(C1,A1&A1)-3),3) E1 =RIGHT(D1)&MID(D1,2,1)&LEFT(D1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

I used 3 related formulas, in C, D, and E, and the final result in E should match your examples in F, but 2 don't match.

Formula C just figures out whether SOU or UOS (SOU reversed) is found. Formula D removes that from the word. And formula E reverses the word, since your results are reversed from what I'd expect (part of the rules I don't get). The 2 rows marked with ? I don't understand at all why you say what they should be.

Let me know if this is close or not.

 

[Michael M]

I don't believe you will get a formula to do it, but you may be able to use this modified function from ExtendOffice

Option Compare Text
Function Sc(oldname)
    On Error Resume Next
    oldname = Replace(oldname, "sou", "")
    oldname = Replace(oldname, "ous", "")
    oldname = Replace(oldname, "uos", "")
    oldname = Replace(oldname, "suo", "")
    n = Len(oldname)
    newname = ""
    Do
        i = Int(Rnd() * n) + 1
        c = Mid(oldname, i, 1)
        If c <> "*" Then
            newname = newname & c
            oldname = Replace(oldname, c, "*", , 1)
        End If
    Loop Until Len(newname) = n
    Sc = LCase(newname)
End Function

Excel 2007
	A	B	C
1
2	southe	hte
3	SOURON	onr
4	SOUTER	ter
5	OUSELS	lse
6	SOUSED	dse
7	XESOUT	xte
8	AQUOSE	eqa
9
Sheet1

Cell Formulas
Range	Formula
B2	=Sc(A2)
B3	=Sc(A3)
B4	=Sc(A4)
B5	=Sc(A5)
B6	=Sc(A6)
B7	=Sc(A7)
B8	=Sc(A8)

 

[Michael M]

@Eric W
Well, that crushed my first statement...

 

[BlackieHamel]

@Eric W
Well, that crushed my first statement...

Thanks, guys, for those incredibly fast replies. I think you have convinced me that Excel /VBA *can* do what I asked, but I don't think either one quite has it down yet.

The given here is that the first line has SOU, in that order. The objective is to find a 3-letter string that produces a given 6-letter string when placed directly under the SOU, and then, reading in either direction -- and starting anywhere. So, for XESOUT, there's only one solution:
SOU
EXT

For AQUOSE, too, there's only one solution, this time reading counterclockwise:
SOU
EAQ

Is that any clearer?

Blackie

 

[Michael M]

Can you explain the logic of this ??

So, for XESOUT, there's only one solution:
SOU
EXT

Why not XET, TXE or ETX ??

 

[BlackieHamel]

Can you explain the logic of this ??



Why not XET, TXE or ETX ??

Because you have to be able to read XESOUT by starting at the X and going either clockwise or counterclockwise from that point.

SOU
EXT

works, but no other combination of E, X, and T accomplishes that.


Blackie

 

[Michael M]

Is that any clearer?

No.....and while I see the "pattern", I'm guessing only a Regex answer will be able to follow that pattern.
I think one of the MVP's will need to have input 'cause I can't see how Excel will be able to determine clock or counter !!
If you don't get any further inpu, I'd keep bumping it up over the next few days to see if it attracts a player

 

[XOR LX]

Hi,

What version of Excel is this for?

Regards

 

[BlackieHamel]

Hi,

What version of Excel is this for?

Regards

It's Excel for Office 365 MSO ver 16.0.[etcetera]

Blackie