Remove 3 letters from each text string

BlackieHamel

Board Regular
Joined
May 9, 2014
Messages
93
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]
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
I'm not entirely clear on your rules, but consider this:

ABCDEFGH
SOURONSOURONNORNOR
SOUTERSOUTERRETRET
OUSELSSOUSELLESLES
SOUSEDSOUSEDDESDES
XESOUTSOUXETTEXEXT?
AQUOSEUOSAQEEQAEAQ?

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>
Sheet2

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH("SOU",A1&A1)),"SOU","UOS")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D1[/TH]
[TD="align: left"]=MID(SUBSTITUTE(A1&A1,C1,""),MAX(1,SEARCH(C1,A1&A1)-3),3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E1[/TH]
[TD="align: left"]=RIGHT(D1)&MID(D1,2,1)&LEFT(D1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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.
 
Upvote 0
I don't believe you will get a formula to do it, but you may be able to use this modified function from ExtendOffice

Code:
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
ABC
1
2southehte
3SOURONonr
4SOUTERter
5OUSELSlse
6SOUSEDdse
7XESOUTxte
8AQUOSEeqa
9
Sheet1
Cell Formulas
RangeFormula
B2=Sc(A2)
B3=Sc(A3)
B4=Sc(A4)
B5=Sc(A5)
B6=Sc(A6)
B7=Sc(A7)
B8=Sc(A8)
 
Last edited:
Upvote 0
@Eric W
Well, that crushed my first statement...:beerchug:

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
 
Upvote 0
Can you explain the logic of this ??

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

Why not XET, TXE or ETX ??
 
Upvote 0
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
 
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,217
Members
453,024
Latest member
Wingit77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top