Searching and appending "The "

[rmanke]

Hello,

This is my first post to the forum, so please be kind...

I tried searching everywhere, but the terminology is so common that I cannot find a solution to my problem.

I would like to determine a search and append function that will search for the word "The " at the beginning of a cell, and if it exists, trim it, and append it to the end of the cell in a single column or in multiple columns.

Two examples:

BEFORE:
"The Beatles"
"The Beginning and the End"

AFTER:
"Beatles, The"
"Beginning and the End, The"


Can anyone point me to a good example, or help me out? It would be much appreciated!

Thanks in advance!

 

[anandvarma]

try this

=TRIM(MID(A1,SEARCH("The",A1)+3,LEN(A1)))&" The"

 

[Robert Mika]

Try this:
=IF(EXACT(TRIM(LEFT(A1,FIND(" ",A1))),"The"),TRIM(LEFT(A1,FIND(" ",A1)))&", "&RIGHT(A1,LEN(A1)-4),"")

 

[anandvarma]

small mistake, forgot to put ,

text has to at cell a1

=TRIM(MID(A1,SEARCH("The",A1)+3,LEN(A1)))&", The"

 

[Michael M]

Hi and Welcome to the Board.
Assuming ALL cells have "The" at the start
In an adjacent column try

=IF(LEFT(A1,4)="The ",REPLACE(A1,1,4,),"")&", "&"The "

 

[Robert Mika]

small mistake, forgot to put ,

text has to at cell a1

=TRIM(MID(A1,SEARCH("The",A1)+3,LEN(A1)))&", The"

anandvarma,
your formula will work for whole text(string) but the request is only fot the first word.

 

[Rick Rothstein]

One more for you to consider...

=IF(LEFT(A1,4)="The ",MID(A1,5,999)&", The",A1)

 

[rmanke]

Awesome, thanks folks!

No, not all fields have "The " at the beginning, so I need to recursively check the fields.

I'm helping a friend with his karaoke show list, which has over 40,000 rows in a spreadsheet, so this will save a lot of time.

Thanks!

 

[Sektor]

[COLOR="Blue"]Function[/COLOR] AttachThe(Str [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    Application.Volatile
    [COLOR="Blue"]Dim[/COLOR] s [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    [COLOR="Blue"]With[/COLOR] CreateObject("VBScript.RegExp")
        .Pattern = "^""\s*The"
        s = Trim$(.Replace(Str, ""))
        AttachThe = """" & Left(s, Len(s) - 1) & ", The"""
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]

 

[rmanke]

Wow, you guys are fast!

Thanks!