Moving Part of Column

  • Thread starter Thread starter Michael Koerner
  • Start date Start date
M

Michael Koerner

I have Heading = Last Name in Col A This column has a lot of names that contain (now xxxxx) which is their married name I would like to move all of the (now xxxxx) to Col B which has a heading of Married Name so that I can sort properly on Col A. Can this be done easily or do I have to do it one record at a time.
 
You can do this quite easily manually. Select the entire column and click Edit/Replace from the menu bar. Put " now " (without the quote marks... just make sure you have a blank space at the beginning and end of the text) into the "Find what" field, put any character that will never be found in a name (I am using the greater than symbol, >) into the "Replace with" field, click the "Options>>" button (note the double greater than symbol) to open the extra options and if there is a check mark in the "Match entire cell contents", remove it. Now click the "Replace All" button. Next, while the column is still selected, click Data/TextToColumns from the menu bar... click "Delimited" on Step 1, click "Other" and put the symbol (>) in the blank field next to it on Step 2 and then click the "Finish" button. And, if you want a macro to do all that, something like this should work...

Sub SplitOutMarriedNames()
Dim C As Range
For Each C In Range("A:A")
If InStr(1, C.Value, " now ", vbTextCompare) Then
C.Value = Replace(C.Value, " now ", ">", , , vbTextCompare)
End If
Next
Range("A:A").TextToColumns Range("A1"), xlDelimited, , , _
False, False, False, False, True, ">"
End Sub
 
Rick;

Sorry, could not get either to work. I'm using Excel 2007 (should have mentioned that earlier) I guess it would have been easier if I said that I wanted to move from Col A what ever is in brackets (xxxxxx) including the brackets to Col B

--

Regards
Michael Koerner


You can do this quite easily manually. Select the entire column and click Edit/Replace from the menu bar. Put " now " (without the quote marks... just make sure you have a blank space at the beginning and end of the text) into the "Find what" field, put any character that will never be found in a name (I am using the greater than symbol, >) into the "Replace with" field, click the "Options>>" button (note the double greater than symbol) to open the extra options and if there is a check mark in the "Match entire cell contents", remove it. Now click the "Replace All" button. Next, while the column is still selected, click Data/TextToColumns from the menu bar... click "Delimited" on Step 1, click "Other" and put the symbol (>) in the blank field next to it on Step 2 and then click the "Finish" button. And, if you want a macro to do all that, something like this should work...

Sub SplitOutMarriedNames()
Dim C As Range
For Each C In Range("A:A")
If InStr(1, C.Value, " now ", vbTextCompare) Then
C.Value = Replace(C.Value, " now ", ">", , , vbTextCompare)
End If
Next
Range("A:A").TextToColumns Range("A1"), xlDelimited, , , _
False, False, False, False, True, ">"
End Sub
 
Try this code...

Sub SplitOutMarriedNames()
Dim C As Range
Dim Bracket As Long
For Each C In Range("A:A")
Bracket = InStr(C.Value, "(")
If Bracket > 0 Then
C.Offset(, 1).Value = Mid(C.Value, Bracket)
C.Value = RTrim(Left(C.Value, Bracket - 1))
End If
Next
End Sub

--
Rick (MVP - Excel)


Rick;

Sorry, could not get either to work. I'm using Excel 2007 (should have mentioned that earlier) I guess it would have been easier if I said that I wanted to move from Col A what ever is in brackets (xxxxxx) including the brackets to Col B

--

Regards
Michael Koerner


You can do this quite easily manually. Select the entire column and click Edit/Replace from the menu bar. Put " now " (without the quote marks... just make sure you have a blank space at the beginning and end of the text) into the "Find what" field, put any character that will never be found in a name (I am using the greater than symbol, >) into the "Replace with" field, click the "Options>>" button (note the double greater than symbol) to open the extra options and if there is a check mark in the "Match entire cell contents", remove it. Now click the "Replace All" button. Next, while the column is still selected, click Data/TextToColumns from the menu bar... click "Delimited" on Step 1, click "Other" and put the symbol (>) in the blank field next to it on Step 2 and then click the "Finish" button. And, if you want a macro to do all that, something like this should work...

Sub SplitOutMarriedNames()
Dim C As Range
For Each C In Range("A:A")
If InStr(1, C.Value, " now ", vbTextCompare) Then
C.Value = Replace(C.Value, " now ", ">", , , vbTextCompare)
End If
Next
Range("A:A").TextToColumns Range("A1"), xlDelimited, , , _
False, False, False, False, True, ">"
End Sub
 
Much appreciated, thanks Rick

--

Regards
Michael Koerner


Try this code...

Sub SplitOutMarriedNames()
Dim C As Range
Dim Bracket As Long
For Each C In Range("A:A")
Bracket = InStr(C.Value, "(")
If Bracket > 0 Then
C.Offset(, 1).Value = Mid(C.Value, Bracket)
C.Value = RTrim(Left(C.Value, Bracket - 1))
End If
Next
End Sub

--
Rick (MVP - Excel)


Rick;

Sorry, could not get either to work. I'm using Excel 2007 (should have mentioned that earlier) I guess it would have been easier if I said that I wanted to move from Col A what ever is in brackets (xxxxxx) including the brackets to Col B

--

Regards
Michael Koerner


You can do this quite easily manually. Select the entire column and click Edit/Replace from the menu bar. Put " now " (without the quote marks... just make sure you have a blank space at the beginning and end of the text) into the "Find what" field, put any character that will never be found in a name (I am using the greater than symbol, >) into the "Replace with" field, click the "Options>>" button (note the double greater than symbol) to open the extra options and if there is a check mark in the "Match entire cell contents", remove it. Now click the "Replace All" button. Next, while the column is still selected, click Data/TextToColumns from the menu bar... click "Delimited" on Step 1, click "Other" and put the symbol (>) in the blank field next to it on Step 2 and then click the "Finish" button. And, if you want a macro to do all that, something like this should work...

Sub SplitOutMarriedNames()
Dim C As Range
For Each C In Range("A:A")
If InStr(1, C.Value, " now ", vbTextCompare) Then
C.Value = Replace(C.Value, " now ", ">", , , vbTextCompare)
End If
Next
Range("A:A").TextToColumns Range("A1"), xlDelimited, , , _
False, False, False, False, True, ">"
End Sub
 
Back
Top