PDA

View Full Version : If M column starts with phone number shift K,L and M cells left.



techsavvybro
09-13-2016, 07:56 AM
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
09-13-2016, 08:13 AM
Weird, can you elaborate on how you actually use the macro? Maybe im not doing something right..

SamT
09-13-2016, 08:45 AM
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