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.

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

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

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

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

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

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