If M column starts with phone number shift K,L and M cells left.

[techsavvybro]

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

[techsavvybro]

Weird, can you elaborate on how you actually use the macro? Maybe im not doing something right..

[SamT]

Weird, Your sample data only includes Columns("I:M"), but your problem is in Columns("J:N")

Try replacing this

Cells(thisRow, "J").Delete xlShiftToLeft

with

Cells(thisRow, "J").Cut
Cells(thisRow, "O").Insert
Application.CutCopyMode = False