Formula or code to remove values from string

[JP Romano]

Hi all,
Having some trouble with the syntax to translate the content of a string. The string will always contain stock tickers, but the format varies. I need to normalize them into something like ticker(space)exchange code.

For example, cell 1 can contain:
ABC.EF US - I need to remove the .EF so I'm left with ABC US
ABCDEF.GH TT - I need to remove the .GH so I'm left with ABCDEF TT

The length of the part preceeding the "." will vary, as will the last two characters (the exchange code). I expect that there will always be two characters immediately after the "." which are to be removed.

Thanks for any help!

JP

[rwgrietveld]

In case there is always 1 DOT and 1 SPACE then

Please Login or Register  to view this content.

It takes the left part before the DOT and the right part after the SPACE

[NBVC]

Try

=Trim(Replace(A1,Find(".",A1),3,""))

[JP Romano]

Excellent...thanks a million to both of you! Working like a charm.