Additional condition ... if ..then..

[Frank Bacchus]

Hi All:

I would like to use the following formula ONLY IF the content in the A column do not contain a ",". If it contains a "," I want to use it as-is. What do I need to add to this formula to make that happen?

=MID(A2,FIND(",",A2,1)+1,LEN(A2))&", "&MID(A2,1,FIND(",",A2,1)-1)

Thanks in advance:

frank

 

[p45cal]

=iferror(MID(A2,FIND(",",A2,1)+1,LEN(A2))&", "&MID(A2,1,FIND(",",A2,1)-1),A2)

?

 

[bosco_yip]

Another option :

=TRIM(MID(A2&", "&A2,FIND(",",A2&", ",1)+1,LEN(A2)+1))

Regards
Bosco

 

[Frank Bacchus]

=iferror(MID(A2,FIND(",",A2,1)+1,LEN(A2))&", "&MID(A2,1,FIND(",",A2,1)-1),A2)

?

This doesn't quite do it: Here is what's happening:
Cell A2 = Abaxxa, Ian Results, with Formula: Ian, Abaxxa -- I need Abaxxa, Ian
Cell A2 = Antoinette LaPorche, with Formula: Antoinette LaPorche -- I need LaPorche, Antoinette

Thanks

frank

 

[Frank Bacchus]

Bosco:

This doesn't quite do it: Here is what's happening:
Cell A2 = Abaxxa, Ian Results, with Formula: Ian, Abaxxa -- I need Abaxxa, Ian
Cell A2 = Antoinette LaPorche, with Formula: Antoinette LaPorche -- I need LaPorche, Antoinette

Thanks

frank

Another option :

=TRIM(MID(A2&", "&A2,FIND(",",A2&", ",1)+1,LEN(A2)+1))

Regards
Bosco

 

[Shteven]

This solution works as long as your data conforms to the examples above: two words with one space between them and a comma after only one of the words.

=IF(RIGHT(M1,1)=",",MID(M1,SEARCH(" ",M1)+1,9^9)&" "&LEFT(M1,SEARCH(" ",M1)-1),M1)

If there are most permutations of your input, then I would recommend attaching a sample file.

 

[Frank Bacchus]

This solution works as long as your data conforms to the examples above: two words with one space between them and a comma after only one of the words.

=IF(RIGHT(M1,1)=",",MID(M1,SEARCH(" ",M1)+1,9^9)&" "&LEFT(M1,SEARCH(" ",M1)-1),M1)

If there are most permutations of your input, then I would recommend attaching a sample file.

Bosco:

It still is not converting correctly. Here is a sample spreadsheet. let me know what your thoughts are?

Thanks

frank

 

[bosco_yip]

Bosco:
It still is not converting correctly. Here is a sample spreadsheet. let me know what your thoughts are? Thanks
frank

Try..............

C2, copy down:

=IF(ISNUMBER(FIND(",",A2)),A2,TRIM(MID(A2&", "&A2,FIND(" ",A2)+1,LEN(A2)+1)))

Regards
Bosco

 

[John Jairo V]

Hi to all!

Same idea by @bosco_yip, but a little shorter:

=IF(COUNTIF(A2,"*,*"),A2,MID(A2&", "&A2,1+FIND(" ",A2),1+LEN(A2)))

Blessings!