How to replace last comma with 'and' or '&' in a string with multiple commas ?

[Yousaf Shah]

Hello everybody
Thank you all for helping others.
I have a very simple question that how can we replace only last comma with 'and' in string, which has got multiple commas?
Suppose we have a sentence "I like Banana, Orange, Grapes, Mango in fruits". I have tried Replace() function but that put 'and' in place of every comma, which is fine if there are only two items. But I want that this sentence must be converted to "I like Banana, Orange, Grapes and Mango in fruits", replacing only the last comma, when there are more than two items and replace the only comma in case when there are only two items.
Hope I have made my question understandable.

[zmbd]

"I like Banana, Orange, Grapes and Mango in fruits"
vs
"I like Banana, Orange, Grapes, and Mango in fruits"
For most people, the last comman should remain in the list prior to the final and/or/conjuctive.

----------

COMMAS

A standard use for commas is to separate the items in a series: “cats, dogs, and gerbils.” Authorities differ as to whether that final comma before the “and” is required. Follow the style recommended by your teacher, editor, or boss when you have to please them; but if you are on your own, I suggest you use the final comma. It often removes ambiguities.

----------
So unless you have a teacher or editor that doesn't like the final comma, I would not be doing this.

This is very much like a homework problem.
So instead of simply posting the code I used to solve something along the same lines, I'll describe the logic.

As for finding the final comma in a sentence:
use the instr() function and the len() function.
do Loop
Instr() Find the first occurance
If the returned is non-zero then add one to the position returned, if > len then the position returned is your last comma else use the new increment as the starting point for your instring.
If the position returned is zero and the last returned was less than the len() of the string then the last position returned was the final comma
use the replace function using the optional start postion at the last instring
Replace Function

Please post the code you have come up with... at that point I'll be happy to help optimize it.

[TjabbeTjibsma]

Am I missing something? I would think you could use the instrRev() function to locate the position of the last comma?

[zmbd]

nope, instrRev() could be used... I forget about that one as it's fairly new to the scene (well for me anyway... guess it was first available with ACC2000; however I didn't start really considering it until just recently as all of the old ACC97 databases at my company have finally been forced to move to ACC2010 (evil laugh).)

[NeoPa]

I would think you could use the InStrRev() function to locate the position of the last comma?

Indeed you could. That would be the most straightforward approach.

NB. When Z says Nope, he means you aren't missing anything. Not that your suggestion was wrong.

PS. I would add that, as a native of England, I find it most common here to omit the last comma from a sentence when the "and" or "or" is used. The alternative, proposed by Z, is often referred to as "The Oxford Comma".

[ADezii]

1. I would take a much different approach to solve this problem:
   

   Public Function fProcessString(strString As String) As String
   Dim varSplit As Variant
   Dim strBuild As String
    
   varSplit = Split(strString, ",")
    
   'Determine how many Items are seperated by Commas
   Select Case UBound(varSplit)
     Case 0
       fProcessString = strString      'Return String
     Case 1                            '1 Comma
       fProcessString = varSplit(LBound(varSplit)) & " and " & varSplit(UBound(varSplit))
     Case Else
       strBuild = Left$(strString, InStrRev(strString, ",") - 1)
       strBuild = strBuild & " and " & Mid$(strString, InStrRev(strString, ",") + 1)
         fProcessString = strBuild
   End Select
   End Function
    

2. Sample Function Calls:
   

   Debug.Print fProcessString("Bananas")
   Debug.Print fProcessString("I like Bananas, Grapes")
   Debug.Print fProcessString("I like Bananas, Grapes, Pineapples")
   Debug.Print fProcessString("I like Bananas, Grapes, Pineapples, Peaches, Apricots")
    

3. Results:
   

   Bananas
   I like Bananas and  Grapes
   I like Bananas, Grapes and  Pineapples
   I like Bananas, Grapes, Pineapples, Peaches and  Apricots

[zmbd]

ADezii,
I had thought about the spilit function; however, it just seemd to involve more steps; however lines 14,15 are cool.

[Yousaf Shah]

Thanks ADezii
Its really cool and working fine...
Thanks once again

[Yousaf Shah]

I have made only one change to function suggested by ADezii, that I have removed after 'and' in line 12 and 15 to make only one space on both sides of and... Rest its wonderful.

[NeoPa]

Am I missing something? I don't see that Split() (A wonderful function I use a great deal.) adds anything at all to this process. It doesn't help find the correct comma and only adds code which has a nett value of getting back to where you started from.

Of course, I'm happy (If, I suspect, a little embarrassed) to learn I've missed something obvious (or otherwise) here.

PS. Yousaf, well spotted.
I have to say, that's a very rare miss from ADezii. As you can see, he always tests his posted code. Even for very small bits like this.

I may sometimes (rarely) question his logic, but never his approach ;-)

[narendersingh]

Thanx ADezii. Its help me too.

[ADezii]

You are quite welcome.

[NeoPa]

To illustrate how straightforward the function could be :

Private Function FixAnd(strIn As String) As String
    Dim lngX As Long
 
    lngX = InStrRev(StringCheck:=strIn, StringMatch:=",")
    FixAnd = IIf(lngX > 0, Left(strIn, lngX - 1) & " and ", "") _
           & Mid(strIn, lngX + 1)
End Function

As I say, there is no need to complicate it further.

For the Oxford Comma version it is simply required to change the literal string " and ", in line #5, to ", and ".

[zmbd]

( BIG SMILE ) You Brits messing up the English language ( BIG SMILE )

Be nice if the world would agree on one standard (^-^).
Here in the US, for all my short life of some 4+decades the Oxford Comma was taught as the standard. Indeed, my oldest is being taught that way now... and very naively, I thought that was the standard!

Not only do I get to learn something new in programming, I get to learn something about the rest of the world too.

IT's Great!

BOL
-z