Need formula that finds the last ", " in a text string & substitutes it w/ the word "and"

**danielneedssomehelp** · 08-21-2014, 08:43 PM

Hello, I have a text string that might have any number of words in it, all separated by a comma and a space. So one text string might be "dog, cat, boy, girl" and another one might be "dog, cat". I need a formula that will find the last comma and space, and substitute that with the word "and". So instead of the text string stating "dog, cat, boy, girl" it will read "dog, cat, boy and girl".

Any help would be much appreciated!

**mehmetcik** · 08-21-2014, 09:00 PM

Put your text in A1

Please Login or Register  to view this content.

**danielneedssomehelp** · 08-21-2014, 09:27 PM

Oh shoot, forgot one thing... if there is no ", " in the text string, then it leaves the text as it was. Sorry!

**Tony Valko** · 08-21-2014, 09:39 PM

Try this...

=IF(COUNT(FIND(",",A1)),SUBSTITUTE(A1,","," and",LEN(A1)-LEN(SUBSTITUTE(A1,",",""))),T(A1))

**Vikas_Gautam** · 08-21-2014, 10:00 PM

Try this..
Inspired from TONY..

Formula:

Please Login or Register  to view this content.

Don't forget to click *

**danielneedssomehelp** · 08-21-2014, 10:02 PM

Thanks Tony, the only problem with that formula is that it only finds "," and not ", ". So I need it to find and substitute the comma with the space after it.

**danielneedssomehelp** · 08-21-2014, 10:05 PM

Ding, ding, ding!!! That did it! Thank you Vikas, Tony and mehmetcik!!! I'll big up you guys!

**Tony Valko** · 08-21-2014, 10:10 PM

Originally Posted by Vikas_Gautam

=SUBSTITUTE(A1,", "," and ",LEN(A1)-LEN(SUBSTITUTE(A1,", "," ")))

If there's no comma that returns a VALUE! error.

**Tony Valko** · 08-21-2014, 10:11 PM

Originally Posted by danielneedssomehelp

Thanks Tony, the only problem with that formula is that it only finds "," and not ", ". So I need it to find and substitute the comma with the space after it.

Well, based on the samples you posted it didn't matter.

I see you're satisfied so that's all that matters!

**gak67** · 08-21-2014, 10:11 PM

Originally Posted by danielneedssomehelp

Thanks Tony, the only problem with that formula is that it only finds "," and not ", ". So I need it to find and substitute the comma with the space after it.

Are there instances of comma's without the space? If not, it should make no difference.

"dog, cat" with the "," replaced with " and" will be 'dog and cat".

"dog, cat" with the ", " replaced with " and " will be 'dog and cat".

**danielneedssomehelp** · 08-21-2014, 10:20 PM

Tony, you caught that!!! Yes, there are instances where there is no comma with a space, and I get an error. Can you help??

**Vikas_Gautam** · 08-21-2014, 10:25 PM

Try this..

Formula:

Please Login or Register  to view this content.

Don't forget to click *, again

**Tony Valko** · 08-22-2014, 08:45 AM

Originally Posted by danielneedssomehelp

Tony, you caught that!!! Yes, there are instances where there is no comma with a space, and I get an error. Can you help??

Can you post SEVERAL representative examples?

Based on your explanation in post #1 the formula in post #4 does what you asked for.

**danielneedssomehelp** · 08-23-2014, 11:29 AM

Yes! That is it!!! Thank you Vikas- works perfect! Thank you to all!

**danielneedssomehelp** · 08-23-2014, 11:31 AM

My fault for not adding that last detail, but you caught it Tony, so thank you!!!! I got it sorted, but couldn't have done it without you!

**Tony Valko** · 08-23-2014, 04:06 PM

Good deal. Thanks for the feedback!

Need formula that finds the last ", " in a text string & substitutes it w/ the word "and"

LinkBack

Thread Tools

Rate This Thread

Display

Need formula that finds the last ", " in a text string & substitutes it w/ the word "and"

Re: Need formula that finds the last ", " in a text string & substitutes it w/ the word "a

Re: Need formula that finds the last ", " in a text string & substitutes it w/ the word "a

Re: Need formula that finds the last ", " in a text string & substitutes it w/ the word "a

Re: Need formula that finds the last ", " in a text string & substitutes it w/ the word "a

Re: Need formula that finds the last ", " in a text string & substitutes it w/ the word "a

Re: Need formula that finds the last ", " in a text string & substitutes it w/ the word "a

Re: Need formula that finds the last ", " in a text string & substitutes it w/ the word "a

Re: Need formula that finds the last ", " in a text string & substitutes it w/ the word "a

Re: Need formula that finds the last ", " in a text string & substitutes it w/ the word "a

Re: Need formula that finds the last ", " in a text string & substitutes it w/ the word "a

Re: Need formula that finds the last ", " in a text string & substitutes it w/ the word "a

Re: Need formula that finds the last ", " in a text string & substitutes it w/ the word "a

Re: Need formula that finds the last ", " in a text string & substitutes it w/ the word "a

Re: Need formula that finds the last ", " in a text string & substitutes it w/ the word "a

Re: Need formula that finds the last ", " in a text string & substitutes it w/ the word "a

Thread Information

Users Browsing this Thread

Similar Threads

create text string: "NOT "[b2value]", NOT "[b3value]", ..., NOT "[b600value]"

[SOLVED] Combo list selected by "contains" text string entered rather than "begins with"

How to check if a range of cells contains the word "unresponsive" and return "yes" or "no"

[SOLVED] How to Count number of "Error" and "OK" after the word "Instrument" found in table row

Formula returning result of "True" or "False" instead of "1" or "0"

Bookmarks

Bookmarks

Posting Permissions