Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
test text string for a comma (,)
Excel XP on WinXP
Inherited a spreadsheet from another user. Have a mixture of lname, fname and fname lname combinations. I can successfully convert the lname, fname to fname lname, teh problem is those cells that are already in the fname lname return a #Value error. I thought I could find a way to logically test the string for a comma(,) and use an If statement to control my concatenate formula. I can't find a way to test logically for the comma. Can anyone offer a solution? Thank you |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
test text string for a comma (,)
Thanks for allowing me to post the problem. Finished teh post and searched
one more time, came up with teh ISERR and applied that. Worked great! here is what I finally used =IF(ISERR(RIGHT(A61,(LEN(A61)-FIND(",",A61)-1))&" "&LEFT(A61,FIND(",",A61)-1)),A61,RIGHT(A61,(LEN(A61)-FIND(",",A61)-1))&" "&LEFT(A61,FIND(",",A61)-1)) Where Col A contained a combination of "fname lname" and "lname, fname" entries. Maybe this will help someone else down the road! "JR Hester" wrote: Excel XP on WinXP Inherited a spreadsheet from another user. Have a mixture of lname, fname and fname lname combinations. I can successfully convert the lname, fname to fname lname, teh problem is those cells that are already in the fname lname return a #Value error. I thought I could find a way to logically test the string for a comma(,) and use an If statement to control my concatenate formula. I can't find a way to test logically for the comma. Can anyone offer a solution? Thank you |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
test text string for a comma (,)
=if(isnumber(search(a1&" ",",")),"has comma", "no comma")
(a1&" " will avoid "has comma" when A1 is empty) =if(countif(a1,"*,*")0,"has comma","no comma") is another way. =len(a1)-len(substitute(a1,",","")) will return the number of commas in A1 JR Hester wrote: Excel XP on WinXP Inherited a spreadsheet from another user. Have a mixture of lname, fname and fname lname combinations. I can successfully convert the lname, fname to fname lname, teh problem is those cells that are already in the fname lname return a #Value error. I thought I could find a way to logically test the string for a comma(,) and use an If statement to control my concatenate formula. I can't find a way to test logically for the comma. Can anyone offer a solution? Thank you -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
test text string for a comma (,)
Try this:
=IF(COUNT(FIND(",",A1)),MID(A1&" "&A1,FIND(",",A1)+2,LEN(A1)-1),A1) Lname, Fname will be reversed to Fname Lname Fname Lname will not change I didn't test this on names with middle intials or with multiple first and last names: Billy Bob B. Jones Jr De La Hoya, Oscar Jr If you have names like that....good luck! -- Biff Microsoft Excel MVP "JR Hester" wrote in message ... Thanks for allowing me to post the problem. Finished teh post and searched one more time, came up with teh ISERR and applied that. Worked great! here is what I finally used =IF(ISERR(RIGHT(A61,(LEN(A61)-FIND(",",A61)-1))&" "&LEFT(A61,FIND(",",A61)-1)),A61,RIGHT(A61,(LEN(A61)-FIND(",",A61)-1))&" "&LEFT(A61,FIND(",",A61)-1)) Where Col A contained a combination of "fname lname" and "lname, fname" entries. Maybe this will help someone else down the road! "JR Hester" wrote: Excel XP on WinXP Inherited a spreadsheet from another user. Have a mixture of lname, fname and fname lname combinations. I can successfully convert the lname, fname to fname lname, teh problem is those cells that are already in the fname lname return a #Value error. I thought I could find a way to logically test the string for a comma(,) and use an If statement to control my concatenate formula. I can't find a way to test logically for the comma. Can anyone offer a solution? Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract the text between last comma and last but one comma. | Excel Worksheet Functions | |||
Need to test for a specific text string and write value to another | Excel Worksheet Functions | |||
Test String | Excel Discussion (Misc queries) | |||
how do i concatenate a string and cell formula and use a comma? | Excel Worksheet Functions | |||
need to remove a comma from end of test in cells | Excel Worksheet Functions |