Find first repeated word in a text

[#1]

I have the table below in .csv format:

Records

something-Spiderman-something-something

something-Spiderman-something-X-Man

Spiderman-something-something-something

X-Man-something-Spiderman-something-something-something

X-Man-something-something-something-something

I want to form another column so the result would be like:

Records , TheHero

something-Spiderman-something-something , Spiderman

something-Spiderman-something-X-Man , Spiderman

Spiderman-something-something-something , Spiderman

X-Man-something-Spiderman-something-something-something , X-Man

X-Man-something-something-something-something, X-Man

The "something" value can be any text and it does not follow a format.

The ordering of attributes in the field might be different.

I need to find first occurrence of a word (either "Spiderman" or "X-Man") from the left.

Thank you.

[swuehl]

Not really sure about your requirements, but you can look into Index() function to retrieve the position of a substring found in your text values.

[sunny_talwar]

May be this:

Table:

LOAD Records,

  If(Len(Trim(SubField(Replace(Replace(Records, 'something', '|'), '-', ''), '|', 1))) > 0,

  SubField(Replace(Replace(Records, 'something', '|'), '-', ''), '|', 1),

  SubField(Replace(Replace(Records, 'something', '|'), '-', ''), '|', 2)) as NewRecords;

LOAD * Inline [

Records

something-Hotman-something-something

something-Hotman-something-XMan

Hotman-something-something-something

XMan-something-Hotman-something-something-something

XMan-something-something-something-something

];

[#4]

I add more information to the question:
The "something" can be any text and it does not follow a format, so I can't use replace function.

[petter]

Will it always be just two words you are looking for or will there be a variable number of words? If so how many?

[swuehl]

You can find the first of a variable number of heros like

MAP:
MAPPING LOAD 
 Hero, '\/' & Hero & '/\' as F2
INLINE [
Hero
Hotman
XMan
];

Table:
LOAD Records,
  TextBetween(MapSubString('MAP',Records), '\/','/\',1) as FirstHero,
  TextBetween(MapSubString('MAP',Records), '\/','/\',2) as SecondHero;
LOAD * Inline [
Records
something-Hotman-something-something
something-Hotman-something-XMan
Hotman-something-something-something
XMan-something-Hotman-something-something-something
XMan-something-something-something-something
];

I've used '\/' and '/\' as delimiters for the heros, just use any sequence of characters that won't show up in your "something" text parts.