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!
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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.
 
Upvote 0
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)
 
Upvote 0
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:
 
Upvote 0
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))
 
Upvote 0
=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
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

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
Back
Top