| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change order of string
In A1 I have the string DOE, JOHN PETER MR (SEE SMITH)
What I would like to do is compose a formula that can reorder this string to read MR JOHN PETER DOE (SEE SMITH) However note that MR could be MRS, MS or DR. That is A1 might contain DOE, JANE SALLY MS (SEE SMITH) Can someone help me here please. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Change order of string
Hi Gavin,
Am Fri, 18 Dec 2015 22:45:07 -0800 (PST) schrieb GavinS: In A1 I have the string DOE, JOHN PETER MR (SEE SMITH) What I would like to do is compose a formula that can reorder this string to read MR JOHN PETER DOE (SEE SMITH) However note that MR could be MRS, MS or DR. That is A1 might contain DOE, JANE SALLY MS (SEE SMITH) that would be a long formula. Better try it with a macro. For your examples following code works and writes you the new strings to column B: Sub MoveNames() Dim rngC As Range Dim varWords As Variant Dim i As Long, LRow As Long Dim myStr As String, str1 As String, str2 As String, str3 As String With Sheets("Sheet1") LRow = .Cells(Rows.Count, 1).End(xlUp).Row For Each rngC In .Range("A1:A" & LRow) varWords = Split(rngC, " ") str1 = varWords(UBound(varWords) - 2) & " " Select Case UBound(varWords) Case 4 str2 = varWords(1) & " " & varWords(0) & " " str3 = varWords(3) & " " & varWords(4) Case 5 str2 = varWords(1) & " " & varWords(2) & " " & varWords(0) & " " str3 = varWords(4) & " " & varWords(5) End Select rngC.Offset(, 1) = Replace(str1 & str2 & str3, ",", "") Next End With End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Change order of string
Hi Gavin,
Am Fri, 18 Dec 2015 22:45:07 -0800 (PST) schrieb GavinS: In A1 I have the string DOE, JOHN PETER MR (SEE SMITH) What I would like to do is compose a formula that can reorder this string to read MR JOHN PETER DOE (SEE SMITH) However note that MR could be MRS, MS or DR. That is A1 might contain DOE, JANE SALLY MS (SEE SMITH) this would be the formula: =MID(A1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(A1,"MRS","#"),"MS","#"),"MR","#"),"DR", "#")),IF(ISNUMBER(FIND("MRS",A1)),4,3))&MID(LEFT(A 1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE(A1,"MRS","#"),"MS","#"),"MR","#"),"DR","#"))-1)&" "&LEFT(A1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUT E(SUBSTITUTE(A1,"MRS","#"),"MS","#"),"MR","#"),"DR ","#"))-1),FIND(",",A1)+2,LEN(LEFT(A1,FIND("#",SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"MRS","#"),"MS ","#"),"MR","#"),"DR","#"))-2)))&" "&MID(A1,FIND("(",A1),99) Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Change order of string
Hi Gavin,
Am Sat, 19 Dec 2015 10:40:40 +0100 schrieb Claus Busch: =MID(A1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(A1,"MRS","#"),"MS","#"),"MR","#"),"DR", "#")),IF(ISNUMBER(FIND("MRS",A1)),4,3))&MID(LEFT(A 1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE(A1,"MRS","#"),"MS","#"),"MR","#"),"DR","#"))-1)&" "&LEFT(A1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUT E(SUBSTITUTE(A1,"MRS","#"),"MS","#"),"MR","#"),"DR ","#"))-1),FIND(",",A1)+2,LEN(LEFT(A1,FIND("#",SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"MRS","#"),"MS ","#"),"MR","#"),"DR","#"))-2)))&" "&MID(A1,FIND("(",A1),99) a little bit shorter: =MID(SUBSTITUTE(MID(A1,FIND(",",A1)+2,99)&MID(A1,F IND(",",A1)+2,99),MID(A1,FIND("(",A1),99),""),IF(I SNUMBER(FIND("MRS",A1)),LEN(SUBSTITUTE(MID(A1,FIND (",",A1)+2,99)&MID(A1,FIND(",",A1)+2,99),MID(A1,FI ND("(",A1),99),""))/2-5,LEN(SUBSTITUTE(MID(A1,FIND(",",A1)+2,99)&MID(A1, FIND(",",A1)+2,99),MID(A1,FIND("(",A1),99),""))/2-4)+2,LEN(SUBSTITUTE(MID(A1,FIND(",",A1)+2,99)&MID( A1,FIND(",",A1)+2,99),MID(A1,FIND("(",A1),99),""))/2)&LEFT(A1,FIND(",",A1)-1)&" "&MID(A1,FIND("(",A1),99) Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Change order of string
Hi Gavin,
Am Sat, 19 Dec 2015 11:37:15 +0100 schrieb Claus Busch: a little bit shorter: =MID(SUBSTITUTE(MID(A1,FIND(",",A1)+2,99)&MID(A1,F IND(",",A1)+2,99),MID(A1,FIND("(",A1),99),""),IF(I SNUMBER(FIND("MRS",A1)),LEN(SUBSTITUTE(MID(A1,FIND (",",A1)+2,99)&MID(A1,FIND(",",A1)+2,99),MID(A1,FI ND("(",A1),99),""))/2-5,LEN(SUBSTITUTE(MID(A1,FIND(",",A1)+2,99)&MID(A1, FIND(",",A1)+2,99),MID(A1,FIND("(",A1),99),""))/2-4)+2,LEN(SUBSTITUTE(MID(A1,FIND(",",A1)+2,99)&MID( A1,FIND(",",A1)+2,99),MID(A1,FIND("(",A1),99),""))/2)&LEFT(A1,FIND(",",A1)-1)&" "&MID(A1,FIND("(",A1),99) and again a little bit shorter: =MID(A1,FIND("(",A1)-IF(ISNUMBER(FIND("MRS",A1)),4,3),IF(ISNUMBER(FIND( "MRS",A1)),4,3))&MID(A1,FIND(",",A1)+2,FIND(E1 ,A1)-FIND(",",A1)-LEN(E1)+IF(ISNUMBER(FIND("MRS",A1)),2,1))&LEFT(A1, FIND(",",A1)-1)&" "&MID(A1,FIND("(",A1),99) Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Change order of string
Hi Gavin,
Am Sat, 19 Dec 2015 12:21:48 +0100 schrieb Claus Busch: and again a little bit shorter: =MID(A1,FIND("(",A1)-IF(ISNUMBER(FIND("MRS",A1)),4,3),IF(ISNUMBER(FIND( "MRS",A1)),4,3))&MID(A1,FIND(",",A1)+2,FIND(E1 ,A1)-FIND(",",A1)-LEN(E1)+IF(ISNUMBER(FIND("MRS",A1)),2,1))&LEFT(A1, FIND(",",A1)-1)&" "&MID(A1,FIND("(",A1),99) sorry, I forgot to replace E1. The formula now is: =MID(A1,FIND("(",A1)-IF(ISNUMBER(FIND("MRS",A1)),4,3),IF(ISNUMBER(FIND( "MRS",A1)),4,3))&MID(A1,FIND(",",A1)+2,FIND(MID(A1 ,FIND("(",A1)-IF(ISNUMBER(FIND("MRS",A1)),4,3),IF(ISNUMBER(FIND( "MRS",A1)),4,3)),A1)-FIND(",",A1)-LEN(MID(A1,FIND("(",A1)-IF(ISNUMBER(FIND("MRS",A1)),4,3),IF(ISNUMBER(FIND( "MRS",A1)),4,3)))+IF(ISNUMBER(FIND("MRS",A1)),2,1) )&LEFT(A1,FIND(",",A1)-1)&" "&MID(A1,FIND("(",A1),99) Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Change order of string
Hi Gavin,
Am Sat, 19 Dec 2015 10:28:08 +0100 schrieb Claus Busch: Sub MoveNames() better try: Sub Test() Dim rngC As Range Dim varWords As Variant, varTmp As Variant Dim LRow As Long With Sheets("Sheet1") LRow = .Cells(Rows.Count, 1).End(xlUp).Row For Each rngC In .Range("A1:A" & LRow) varWords = Split(rngC, " ") varTmp = varWords(UBound(varWords) - 2) varWords(UBound(varWords) - 2) = varWords(0) varWords(0) = varTmp rngC.Offset(, 1) = Replace(Join(varWords, " "), ",", "") Next End With End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to change series plotting order without changing legend order? | Charts and Charting in Excel | |||
| changing the order of text in a string | New Users to Excel | |||
| Change 3 letter text string to a number string | Excel Discussion (Misc queries) | |||
| Change tab order from left right to down and across | Excel Discussion (Misc queries) | |||
| Reversing the Order of a String | Excel Worksheet Functions | |||
Linear Mode
