Get text

[neno]

Re: Parsing odd lengths non numeric

Amazing!

I was wondering if someone can help with, sort of, similar situation.

Here is an example;

NEW STORY; my name is Jack. I am six years old. I have 200.00 saved for my new bycicle. EXCELLENT JOB.

I am trying to get the word EXCELLENT from the right side.
I am quite familiar with text functions but it seems in this case some function/or code would save me lots of time.

The only consistency seems to UPPER casing from the right side. As it goes to lower, I don't need that part of sentence anymore.
Unfortunately, there are multiple spaces, commas, full-stops.

Any suggestion is very much appreciated,
Neno

 

[phantom1975]

WELCOME TO THE BOARD!

Here is a UDF that will do what you need:

Book1
	A	B	C	D
1	NEWSTORY;mynameisJack.Iamsixyearsold.Ihave200.00savedformynewbycicle.EXCELLENTJOB.
2
3	EXCELLENT
Sheet1

<font face=Courier New><SPAN style="color:darkblue">Function</SPAN> UPPERRIGHTWORD(ThisCell<SPAN style="color:darkblue">As</SPAN> Range, WhatWord<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Long</SPAN>)<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">String</SPAN><SPAN style="color:darkblue">Dim</SPAN> Counter<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Long</SPAN><SPAN style="color:darkblue">Dim</SPAN> UpperCount<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Long</SPAN><SPAN style="color:darkblue">Dim</SPAN> CurrentWord<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">String</SPAN><SPAN style="color:darkblue">Dim</SPAN> UpperWord<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Boolean</SPAN>
            
    <SPAN style="color:darkblue">For</SPAN> Counter = Len(ThisCell.Value)<SPAN style="color:darkblue">To</SPAN> 1<SPAN style="color:darkblue">Step</SPAN> -1
        <SPAN style="color:darkblue">If</SPAN> Asc(Mid(ThisCell.Value, Counter, 1)) > 64 And Asc(Mid(ThisCell.Value, Counter, 1))< 91<SPAN style="color:darkblue">Then</SPAN>
            UpperWord =<SPAN style="color:darkblue">True</SPAN>
            CurrentWord = Mid(ThisCell.Value, Counter, 1) & CurrentWord
        <SPAN style="color:darkblue">Else</SPAN>
            <SPAN style="color:darkblue">If</SPAN> UpperWord =<SPAN style="color:darkblue">True</SPAN><SPAN style="color:darkblue">Then</SPAN>
                UpperCount = UpperCount + 1
            <SPAN style="color:darkblue">End</SPAN><SPAN style="color:darkblue">If</SPAN>
            UpperWord =<SPAN style="color:darkblue">False</SPAN>
            <SPAN style="color:darkblue">If</SPAN> WhatWord = UpperCount<SPAN style="color:darkblue">Then</SPAN>
                UPPERRIGHTWORD = CurrentWord
                <SPAN style="color:darkblue">Exit</SPAN><SPAN style="color:darkblue">Function</SPAN>
            <SPAN style="color:darkblue">End</SPAN><SPAN style="color:darkblue">If</SPAN>
            CurrentWord = ""
        <SPAN style="color:darkblue">End</SPAN><SPAN style="color:darkblue">If</SPAN>
    <SPAN style="color:darkblue">Next</SPAN> Counter<SPAN style="color:darkblue">End</SPAN><SPAN style="color:darkblue">Function</SPAN></FONT>