Hey guys, I have a spreadsheet of 8000 values that needs to be fixed. Following columns: J Street Address K City, State L Phone number M County
The issue im having is a few of thevalues have a contact name in the J column which is offsetting the entire table and causing problems when I upload the spreadsheet to a Sharepoint list.
Im trying to write a formula that shifts the K,L, M and N column left IF Column M starts with a phone number in the (***) ***-*** format. Can anyone help please? I'm struggling.
Ideally I want every column like this
J - State St
K - Bridgeport, Alabama 35740
L - (256) 495-2211
M- County: Jackson
but there are a few columns that are formatted like this and need to be changed
J - Randy Haynes
K - 4310 Co Rd 38
L - Section, Alabama 35771
M - (256) 744-2099
N - County: Jackson
Here is an example of some of the data in my spreadsheet.
Palm Beach 300 N County Rd Palm Beach, Florida 33480 (561) 838-5420 County: Palm Beach Palm Beach 50 South Military Trail, Suite 101 West Palm Beach, Florida 33415 (561) 616-7000 Palm Beach 600 W Blue Heron Blvd Riviera Beach, Florida 33404 (561) 845-4104 County: Palm Beach Palm Beach 1040 Royal Palm Beach Blvd Royal Palm Beach, Florida 33411 (561) 790-6102 County: Palm Beach Palm Beach 335 SW 2nd Ave South Bay, Florida 33493 (561) 996-2032 Palm Beach 357 Tequesta Dr Tequesta, Florida 33469 (561) 575-6250 County: Palm Beach Palm Beach Nigel Baker 1000 Greenview Shores Blvd Wellington, Florida 33414 (561) 790-6105 Palm Beach Nigel Baker 1060 Wellington Trace Wellington, Florida 33414 (561) 790-6105 Palm Beach Nigel Baker 3411 Southshore Blvd Wellington, Florida 33414 (561) 790-6105 Palm Beach Nigel Baker 9610 Pierson Rd Wellington, Florida 33414 (561) 790-6105 Palm Beach 500 N Dixie Hwy West Palm Beach, Florida 33401 (561) 804-4700 County: Palm Beach Pasco 5919 Main St New Port Richey, Florida 34652 (727) 841-4500 County: Pasco Pasco 7824 Grand Blvd Port Richey, Florida 34668 (727) 816-1910 County: Pasco Pasco 38410 6th Ave Zephyrhills, Florida 33542 (813) 780-0035 County: Pasco
Someone over at MrExcel gave me the following macro but it doesnt seem to work for me. Please help!
Public Sub RemoveContactNames() Dim lastRow As Long Dim thisRow As Long lastRow = Cells(Rows.Count, "M").End(xlUp).Row ' Change "M" to be a column that contains data in the last row if necessary For thisRow = 1 To lastRow If Cells(thisRow, "M").Value Like "(###)*" Then Cells(thisRow, "J").Delete xlShiftToLeft End If Next thisRow End Sub