deleting last half of all cells in a column

jlcaffrey

New Member
Joined
Jan 31, 2005
Messages
2
Looking to delete partial entries from all cells in 2 different columns.

First part: one column has information such as "John Doe - Son" and I want to break it into 2 cells (adjacent columns)--one column with "John Doe" and the other column with "Son". Have many 100s of cells.

Second part: one column with phone numbers and other info in it (ex: 555-555-5555 - home, 444-444-4444 - work, 333-333-3333 - fax) and I only want to keep the first number. I realize I can find/replace the text and thus remove the words "home", "work", etc, but I want to get rid of the corresponding numbers as well. All I want is the first phone number, without any label. Also 100s of cells.

Any suggestions?

Thanks!
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
Is all the data formatted as indicated in your post?

Have you looked at Data>Text to Columns... using delimiters?

Formula for the first part of your question could be something like this:

Cell B1 = TRIM(LEFT(A1, FIND("-", A1)-1))

Cell C1 = TRIM(MID(A1, FIND("-", A1)+1))

where the original name is in cell A1.
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,994
Welcome to the board!

If A1 (name) is always of the form "Cell 1 - Cell 2" (where a hyphen separates the two), try C1 =LEFT(A1,SEARCH("-",A1)-1) and D1 =RIGHT(A1,LEN(A1)-SEARCH("-",A1)-1)

And, if B1 (phone number) always starts ###-###-#### place, try E1 =LEFT(B1,SEARCH(" ",B1)-1)
 

jlcaffrey

New Member
Joined
Jan 31, 2005
Messages
2
For the 1st part of the question (separating names from relationship), yes, all the data is formatted like that. I tried the suggestion, but somehow it just deleted the latter half and didn't put it into another column. Very possible I did something wrong.

For the 2nd part (deleting everything in the cell except the first phone number), the first 200 or so cells are just the phone number. This is when I was manually deleting everything else and then quit. The rest of them are all the phone number followed by text and other phone numbers. When I tried the given suggestion, it said "#VALUE!". Perhaps because the first entries had nothing following them to delete??

I'm not opposed to receiving step by step instructions, either. Especially on a Monday :wink:
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,994
Norie: I think you forgot the third argument in your second formula... maybe =TRIM(MID(A1, FIND("-", A1)+1,LEN(A1)-FIND("-",A1))) ?

For the second part, try =if(iserror(LEFT(B1,SEARCH(" ",B1)-1)),B1,LEFT(B1,SEARCH(" ",B1)-1))
 

viper

Active Member
Joined
Feb 15, 2002
Messages
382
=LEFT(A1,SEARCH("-",A1)-1)
=RIGHT(A1,LEN(A1)-SEARCH("-",A1)-1)

change the A1 in the above formula to the first cell of your data (if a6 then change A1 to A6). Then click the lower right corner of the cell and drag down to the end of your data, do the same for the other formula.

Since you said you have 200 or so cells w/ phone numbers you can automate the process without doing cell by cell by adding a temporary command button to your worksheet, right click the button then click view code. copy this to the command button click event change the range to your range,
if d100:d200 then put that.

Code:
Dim c As Range
    
    Set c = Range("your range")
      
      
      c.Select
      With Selection
        For Each Cell In Selection
            
            ActiveCell.Value = Left(ActiveCell.Value, 12)
            ActiveCell.Offset(1, 0).Activate
        Next Cell
      End With

click the button and the only thing left in those cells should be the first 10 number of the phone number, the other 2 are for the hypens.

HTH
 

Watch MrExcel Video

Forum statistics

Threads
1,123,141
Messages
5,599,966
Members
414,352
Latest member
macquarie_jchan58

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top