Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving Part of Column
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.
-- Regards Michael Koerner |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving Part of Column
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 (MVP - Excel) "Michael Koerner" wrote in message ... 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. -- Regards Michael Koerner |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving Part of Column
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 "Rick Rothstein" wrote in message ... 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 (MVP - Excel) "Michael Koerner" wrote in message ... 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. -- Regards Michael Koerner |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving Part of Column
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) "Michael Koerner" wrote in message ... 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 "Rick Rothstein" wrote in message ... 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 (MVP - Excel) "Michael Koerner" wrote in message ... 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. -- Regards Michael Koerner |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving Part of Column
Much appreciated, thanks Rick
-- Regards Michael Koerner "Rick Rothstein" wrote in message ... 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) "Michael Koerner" wrote in message ... 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 "Rick Rothstein" wrote in message ... 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 (MVP - Excel) "Michael Koerner" wrote in message ... 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. -- Regards Michael Koerner |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
moving part of a column into another column | Excel Worksheet Functions | |||
moving part of information from one cell to another... | Excel Worksheet Functions | |||
Copying Part of a row down part of a column | Excel Discussion (Misc queries) | |||
Moving data from one part of a worksheet to another with a button | Excel Programming | |||
Moving a sheet from a closed spreadsheet & making it part of an open one. | Excel Programming |