Removing part of a cell

M

Michael Koerner

I have a column of names (3000+) which looks like "name & name". I would
like to remove the space & name in all of the cells. any help in writing a
macro to make my life easier, would be greatly appreciated TIA

Using Excel 2007 under XP with all updates.
 
M

Mike H

Hi Michael,

Could you post some actual data and what it should look like after because
I'm not sure what you mean.

Mike
 
B

Bernard Liengme

No need for VBA
Let you values be on Sheet1 beginning in A1
In A1 of Sheet2 enter =LEFT(Sheet1!A1,FIND("&",Sheet1!A1)-2)
Copy this down the column as far as needed

Select all the data on Sheet2; use Copy; with the data still selected, use
Edit | Paste Special with Values specified. Now all the formulas are data,
you can copy this back over the data in Sheet1

best wishes
 
D

Don Guillett

After testing, change the comment to msgbox and uncomment c.value
Sub parsecells()
For Each c In Range("f2:f3000")
MsgBox Left(c, InStr(c, "&") - 1)
'c.value=Left(c, InStr(c, "&") - 1)
Next c
End Sub
 
R

Rick Rothstein

You want to do this removal in place (that is, in the same cell the text is
in), right? Give this macro a try (change the 3 Const statements to match
your actual layout)....

Sub BeforeAmpersand()
Dim X As Long
Dim LastRow As Long

Const StartNamesRow As Long = 2
Const NamesColumn As String = "A"
Const SheetName As String = "Sheet4"

With Worksheets(SheetName)
LastRow = .Cells(.Rows.Count, NamesColumn).End(xlUp).Row
For X = StartNamesRow To LastRow
With .Cells(X, NamesColumn)
.Value = Trim(Split(.Value, "&")(0))
End With
Next
End With
End Sub
 
M

Michael Koerner

Looks something like this
Header Row = LastName FirstName
Data Row = Smith Bill & Mary
Jones Sam & June
Brown Tom & Joyce

Hope this is enough. TIA
--

Regards
Michael Koerner


Hi Michael,

Could you post some actual data and what it should look like after because
I'm not sure what you mean.

Mike
 
M

Michael Koerner

I have no idea what or how the caps got there. Sorry

--

Regards
Michael Koerner


No need for VBA
Let you values be on Sheet1 beginning in A1
In A1 of Sheet2 enter =LEFT(Sheet1!A1,FIND("&",Sheet1!A1)-2)
Copy this down the column as far as needed

Select all the data on Sheet2; use Copy; with the data still selected, use
Edit | Paste Special with Values specified. Now all the formulas are data,
you can copy this back over the data in Sheet1

best wishes
 
M

Michael Koerner

Tried your method, would have worked great if there were not some cells that
only had one name

--

Regards
Michael Koerner


No need for VBA
Let you values be on Sheet1 beginning in A1
In A1 of Sheet2 enter =LEFT(Sheet1!A1,FIND("&",Sheet1!A1)-2)
Copy this down the column as far as needed

Select all the data on Sheet2; use Copy; with the data still selected, use
Edit | Paste Special with Values specified. Now all the formulas are data,
you can copy this back over the data in Sheet1

best wishes
 
M

Michael Koerner

Should add that in FirstName column, not all have &. Sorry about that.

--

Regards
Michael Koerner


Looks something like this
Header Row = LastName FirstName
Data Row = Smith Bill & Mary
Jones Sam & June
Brown Tom & Joyce

Hope this is enough. TIA
--

Regards
Michael Koerner


Hi Michael,

Could you post some actual data and what it should look like after because
I'm not sure what you mean.

Mike
 
R

Rick Rothstein

And exactly how do you want everything to look AFTER the first name column
is processed? That is, do you want the two names, if there are two, to be
placed in separate columns? Or do you just want to retain the first of the
names and remove the other name completely?
 
D

Don Guillett

Sub parsecells()
'added line below to account for NOT finding %
on error resume next
For Each c In Range("f2:f3000")
MsgBox Left(c, InStr(c, "&") - 1)
'c.value=Left(c, InStr(c, "&") - 1)
Next c
End Sub
 
M

Michael Koerner

Rick;

Would like to just keep the first name and remove all the others.

--

Regards
Michael Koerner


And exactly how do you want everything to look AFTER the first name column
is processed? That is, do you want the two names, if there are two, to be
placed in separate columns? Or do you just want to retain the first of the
names and remove the other name completely?
 
B

Bernard Liengme

=IF(ISNUMBER(FIND("&",Sheet1!A2)),LEFT(Sheet1!A2,FIND("&",Sheet1!A2)-2),Sheet1!A2)
 
R

Ron Rosenfeld

I have a column of names (3000+) which looks like "name & name". I would
like to remove the space & name in all of the cells. any help in writing a
macro to make my life easier, would be greatly appreciated TIA

Using Excel 2007 under XP with all updates.
Click to expand...

Select the Names containing the First Names.

Select Data/ Text To Columns
Delimited
Check both the <space> and Other.
Enter ampersand "&" in Other.
At next step, select the second column, and Do Not Import
--ron
 
R

Rick Rothstein

That is what the macro I posted earlier does. I'm not sure if you didn't see
it or if you do not know how to implement it. Here is the macro again (see
the notes after it on how to install it)...

Sub BeforeAmpersand()
Dim X As Long
Dim LastRow As Long

Const StartNamesRow As Long = 2
Const NamesColumn As String = "A"
Const SheetName As String = "Sheet4"

With Worksheets(SheetName)
LastRow = .Cells(.Rows.Count, NamesColumn).End(xlUp).Row
For X = StartNamesRow To LastRow
With .Cells(X, NamesColumn)
.Value = Trim(Split(.Value, "&")(0))
End With
Next
End With
End Sub

To install this macro, press Alt+F11 to go into the VB editor, then click
Insert/Module from its menu bar and then copy/paste the above macro into the
code window that appears. Next, change the 3 Const statements to reflect
your actual setup (first row number with first & last names, column that
those first & last names are in, and the worksheet name that appears on the
tab for the worksheet containing the names. Now, go back to the worksheet,
press Alt+F8, select BeforeAmpersand from the list and click the Run button.
 
M

Michael Koerner

Worked slicker than grease on a doorknob. Thank you very much for saving me
a lot of work, greatly appreciated.

--

Regards
Michael Koerner


=IF(ISNUMBER(FIND("&",Sheet1!A2)),LEFT(Sheet1!A2,FIND("&",Sheet1!A2)-2),Sheet1!A2)
 
M

Michael Koerner

Rick thanks very much for the macro. And really thanks on how to install it.
Greatly appreciated.

--

Regards
Michael Koerner


That is what the macro I posted earlier does. I'm not sure if you didn't see
it or if you do not know how to implement it. Here is the macro again (see
the notes after it on how to install it)...

Sub BeforeAmpersand()
Dim X As Long
Dim LastRow As Long

Const StartNamesRow As Long = 2
Const NamesColumn As String = "A"
Const SheetName As String = "Sheet4"

With Worksheets(SheetName)
LastRow = .Cells(.Rows.Count, NamesColumn).End(xlUp).Row
For X = StartNamesRow To LastRow
With .Cells(X, NamesColumn)
.Value = Trim(Split(.Value, "&")(0))
End With
Next
End With
End Sub

To install this macro, press Alt+F11 to go into the VB editor, then click
Insert/Module from its menu bar and then copy/paste the above macro into the
code window that appears. Next, change the 3 Const statements to reflect
your actual setup (first row number with first & last names, column that
those first & last names are in, and the worksheet name that appears on the
tab for the worksheet containing the names. Now, go back to the worksheet,
press Alt+F8, select BeforeAmpersand from the list and click the Run button.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Integrating information from Word to Excel (not mail merge) 1
Mail Merge Directory Conditional Page Break 0
Grouping Names in Mail Merge 0
Cell data entering slow! 0
Best Practice for Public Variables for Column Headings 2
Start a mail merge upon opening the document 1
Drop down list control value in vba 2
Need help modifying code 0

Top