last three of four

[Khalil Handal]

Hi,
A cell in sheet1 (say B14) has either four words or five words.
I want to copy the value to anothe cell in sheet2 (say to G15) in a way
that:
If B14 has 4 words I should have the last three word. (delete first word
only).
If B14 has five words I should take the last three word. (delete first 2
words).
Note: all words are seperated by spaces.

Is there more than one way to do it?

 

[anon]

Hi,
A cell in sheet1 (say B14) has either four words or five words.
I want to copy the value to anothe cell in sheet2 (say to G15) in a way
that:
If B14 has 4 words I should have the last three word. (delete first word
only).
If B14 has five words I should take the last three word. (delete first 2
words).
Note: all words are seperated by spaces.

Is there more than one way to do it?

Yes. Is that really the question you wanted to ask?

 

[Ron Rosenfeld]

Hi,
A cell in sheet1 (say B14) has either four words or five words.
I want to copy the value to anothe cell in sheet2 (say to G15) in a way
that:
If B14 has 4 words I should have the last three word. (delete first word
only).
If B14 has five words I should take the last three word. (delete first 2
words).
Note: all words are seperated by spaces.

Is there more than one way to do it?

Yes there is more than one way to do it. The formulas below will return the
last three words of a phrase in B14

Method 1
sheet2!G15:
=MID(TRIM(B14),FIND(CHAR(1),SUBSTITUTE(
TRIM(B14)," ",CHAR(1),LEN(TRIM(B14))-LEN(
SUBSTITUTE(TRIM(B14)," ",""))-2))+1,255)

Method 2
Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then use this formula

sheet2!G15: =REGEX.MID(B14,"(\w+\W+){2}\w+\W+?$")

You could also write a User Defined Function in VBA
--ron

 

[Khalil Handal]

Thanks for information.
How would it be done in BVA code please?
I am only a beginner in VBA!

 

[Ron Rosenfeld]

Thanks for information.
How would it be done in BVA code please?
I am only a beginner in VBA!

Here's one way, although there are many:

=========================================
Option Explicit

Function Last3(str As String) As String
Const sPattern As String = "(\w+\W+){2}\w+(\W+)?$"

Dim objRegExp As Object
Dim colMatches As Object

' Create a regular expression object.
Set objRegExp = CreateObject("VBScript.RegExp")

'Set the pattern by using the Pattern property.
objRegExp.Pattern = sPattern

'Set global applicability.
objRegExp.Global = True

'Test whether the String can be compared.
If (objRegExp.Test(str) = True) Then

'Get the matches.
Set colMatches = objRegExp.Execute(str)
Last3 = colMatches(0)
End If

End Function
===============================================
--ron

 

[anon]

"anon" is SO clever, isn't he?

Doubtful. But there is a bit of method to my madness. The question
pre-supposed that the poster already had knowledge of one way to
accomplish what he/she wanted. Why in heavens name should volunteers
take their time to suss out multiple ways of doing things where one of
them is already known to the OP, but the OP didn't feel the need to
lighten the load on the volunteers by sharing that information?

Maybe I just got out of bed on the wrong side that day, but it struck
me as a bit rude to word the question the way it was worded.

I thought my response was quite tempered.

Sorry you disagree.

Jim

 

[Adel Handal]

Thank

Here's one way, although there are many:

=========================================
Option Explicit

Function Last3(str As String) As String
Const sPattern As String = "(\w+\W+){2}\w+(\W+)?$"

Dim objRegExp As Object
Dim colMatches As Object

' Create a regular expression object.
Set objRegExp = CreateObject("VBScript.RegExp")

'Set the pattern by using the Pattern property.
objRegExp.Pattern = sPattern

'Set global applicability.
objRegExp.Global = True

'Test whether the String can be compared.
If (objRegExp.Test(str) = True) Then

'Get the matches.
Set colMatches = objRegExp.Execute(str)
Last3 = colMatches(0)
End If

End Function
===============================================
--ron

 

[Adel Handal]

Hi,
I inserted the code in a new model but when I typed =Last3(C20) where C20
contains 4 words nothing came out in the cell. I wonder if i am missing
something????

 

[Ron Rosenfeld]

Hi,
I inserted the code in a new model but when I typed =Last3(C20) where C20
contains 4 words nothing came out in the cell. I wonder if i am missing
something????

Or maybe I am missing something.

What, exactly, is in C20?

Here is what I did to test:
======================
I created a new workbook.

<alt-F11> opens the VB Editor
I selected the current project in the Project Explorer window
Insert/Module
a window opens

I copied the code I had posted and pasted it into the module that had opened.

C20: Now is the Time
A1: =last3(c20) --> is the time


--ron

 

[Khalil Handal]

Hi Ron,
In C20 I have the following formula:
=VLOOKUP($A$3,Personal!$B$14:$X$60,1,FALSE)
It give nothing in the cell.
But when I tried it in a new workbook it worked just fine.
Any New Ideas????

 

[Khalil Handal]

Note:
$A$3 is Name List where I choose a name from a list and all the values are
looked up and filled in certain cells.

 

[Ron Rosenfeld]

Hi Ron,
In C20 I have the following formula:
=VLOOKUP($A$3,Personal!$B$14:$X$60,1,FALSE)
It give nothing in the cell.
But when I tried it in a new workbook it worked just fine.
Any New Ideas????

What is the result of this formula?

In other words, what do see when you look at C20?
--ron

 

[Adel Handal]

I see four words (only text)

What is the result of this formula?

In other words, what do see when you look at C20?
--ron

 

[Ron Rosenfeld]

I see four words (only text)

Well, with "four words (only text)" in C20, the formula results are:

=last3(c20): --> "words (only text)"

(without the quote marks). That is what I would expect to see.

Since you wrote that the formula worked in a new workbook, but results in a
blank in your current workbook, there must be something about the data input to
the formula, or something in the workbook itself, or something in the manner in
which you entered the formula that is not the same as what I have been
assuming.

I get the same results if I either enter four words, or if I enter a lookup
formula which returns at least four words.

Do your words include non US English characters? Unicode characters?
--ron

 

[Khalil Handal]

YES, My words include non US English characters. Unicode characters.
What efect does this has?

 

[Ron Rosenfeld]

YES, My words include non US English characters. Unicode characters.
What efect does this has?

That makes a great deal of difference. The regular expression pattern that I
used assumed that a "word" was a sequence of characters in the group of
[A-Za-z0-9_] separated by a sequence of characters not in that group. A
character is assumed to be a representation of a single byte.

However, if you are using Unicode characters, then this assumption is incorrect
as characters are represented by two bytes.

So we have to change the Pattern that the regex is using. If we define a
"word" as a sequence of characters separated by <space>'s, perhaps we can
change it to look at <space>'s and non-<space>'s as they are defined in
Unicode. (\u0020)

You could try the following:

Const sPattern As String = "([^\s]+[\s]+){2}[^\s]+([\s]+)?$"

or, if your text is all encoded as unicode, the following might be better:

Const sPattern As String = "([^\u0020]+[\u0020]+){2}[^\u0020]+([\u0020]+)?$"

I don't have an unicode encoded text to test this on, so let me know what
happens.


--ron

 

[Khalil Handal]

Hi Ron,
I used the second option and it worked fine.
thanks.

YES, My words include non US English characters. Unicode characters.
What efect does this has?

That makes a great deal of difference. The regular expression pattern
that I
used assumed that a "word" was a sequence of characters in the group of
[A-Za-z0-9_] separated by a sequence of characters not in that group. A
character is assumed to be a representation of a single byte.

However, if you are using Unicode characters, then this assumption is
incorrect
as characters are represented by two bytes.

So we have to change the Pattern that the regex is using. If we define a
"word" as a sequence of characters separated by <space>'s, perhaps we can
change it to look at <space>'s and non-<space>'s as they are defined in
Unicode. (\u0020)

You could try the following:

Const sPattern As String = "([^\s]+[\s]+){2}[^\s]+([\s]+)?$"

or, if your text is all encoded as unicode, the following might be better:

Const sPattern As String =
"([^\u0020]+[\u0020]+){2}[^\u0020]+([\u0020]+)?$"

I don't have an unicode encoded text to test this on, so let me know what
happens.


--ron

 

[Ron Rosenfeld]

Hi Ron,
I used the second option and it worked fine.
thanks.

Great. Thanks for the feedback. First time I had to work with Unicode.

The \u0020 is the Unicode representation for a <space>. So the pattern is
looking for sequences of NOT<space>'s followed by a <space> to denote words;
with the last word defined as a sequence of NOT<space> optionally followed by
<space> and then the End of the String ($).

There are some Unicode representations where the character is described by two
unicode codes. I'm not sure how Pattern will work in that circumstance.
--ron