Need to trim various prefixed that are in ALL CAPS off of the front of text in a column

[syncguy]

We have song titles in a column that have ALL CAPS prefixes that we use internally to identify the writer/origin of the song. For example:

RSCAT TR Let the Sun Shine In
RSC GHT Night Runner
RD FGH Midnight Mountain


I need to trim the ALL CAPS gibberish off of the front of the titles , leaving simple "Let The Sun Shine In" etc

I have this formula (below) for trimming around a common cluster of characters. Could I play off of this somehow and trim ALL CAPS characters from the Beginning? Any help is much appreciated

=TRIM(LEFT(N2,FIND(" -by- ",N2)-2))

[protonLeah]

Here's a small function:

Please Login or Register  to view this content.

[Tony Valko]

In the posted samples the data you want starts after the 2nd space character.

Will that always be the case?

[syncguy]

Yes Tony , thanks

[syncguy]

Thank you much protonLeah. But how do you implement this type of function ? I only know formulas that begin with '=' . I guess I'm a beginner still

Here's a small function:

Please Login or Register  to view this content.

[Tony Valko]

Try this...

Data Range

	A	B
2	RSCAT TR Let the Sun Shine In	Let the Sun Shine In
3	RSC GHT Night Runner	Night Runner
4	RD FGH Midnight Mountain	Midnight Mountain

This formula entered in B2 and copied down:

=MID(A2,FIND(" ",A2,FIND(" ",A2)+1)+1,100)

[protonLeah]

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
Withe list in column N select the column you want the results in, say column R. Then in R2 enter: =StripCaps(N2) and drag down as needed.

[syncguy]

THANKS protonLeah !

[martindwilson]

just coz i did it this array finds the first lower case letter and returns the string starting one letter earlier
so will work for any number of leading uppercase
=MID(A1,MIN(FIND(CHAR(ROW($A$97:$A$122)),A1&"abcdefghijklmnopqrstuvwxyz"))-1,255)

[syncguy]

Awesome thanks martindwilson!