is there a code that could place a space

[jvoortman]

I have some data running down column A. I need there to be
a space between the companies and their information. ome
companies show street address and city and postal code
while some only show the telephone number. I guess if
there was a way to get the code to put a blank cell above
everytime a cell occurs with a telephone number, the goal
would b accomplished. Any Ideas???? Thansk for any help
you can offer.

 

[jvoortman]

here is a sample of the data,

C A Ferguson & Sons Inc 807-345-2519
181 Clavet Street,
Thunder Bay, ON
P7A 2M3
C S I 705-267-6488
429 Spruce S,
Schumacher, ON
P0N 1G0
C S I 705-268-7011
ON
Cantol Limited 800-387-9773
Cascades Resources A Division Of Cascades Fine Papers
Group Inc 613-741-9655
1250 Leeds Avenue,
Ottawa, ON
K1B 3W3
CCS Enviro-Tech Group 905-857-7676
15 Simpson Road,
Bolton, ON
L7E 1E4
Checkers Marketing Inc 519-455-1346
21 Heather Crescent,
London, ON
N5W 4L6
Chem Wise 416-493-9553
2175 Sheppard Avenue East,
North York, ON
M2J 1W8
CCS Enviro-Tech Group 905-857-7676
15 Simpson Road,
Bolton, ON
L7E 1E4
Checkers Marketing Inc 519-455-1346
21 Heather Crescent,
London, ON
N5W 4L6
Chem Wise 416-493-9553
2175 Sheppard Avenue East,
North York, ON
M2J 1W8
Chemnorth Systems & Service Co Ltd 705-848-6433
30 Timber Road,
Elliot Lake, ON
P5A 2T1
Chemtec Services 519-888-0225
555 Havelock Drive,
Waterloo, ON
N2L 4Z1
Clean Concepts 416-769-9010
325 Weston Road,
Toronto, ON
M6N 4Z9
Cleaning House Ltd The 800-263-8610
Cleaning House Ltd The 905-547-2636
973 Barton Street East,
Hamilton, ON
L8L 3C4
Clorox Company Of Canada 416-221-3373
5700 Yonge Street,
North York, ON
M2M 4K2
Colgate-Palmolive Canada Inc 416-421-6000
99 Vanderhoof Avenue,
East York, ON
M4G 2H6

 

[JE McGimpsey]

If all the telephone numbers are formatted similarly, something like
this will work:


Public Sub SpaceAboveTelephoneNumber()
Dim i As Long
Application.ScreenUpdating = False
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(i, 1).Value Like "[0-9][0-9][0-9]-*" Then _
Cells(i, 1).EntireRow.Insert
Next i
Application.ScreenUpdating = True
End Sub

Substitute for the format you want (or use an Or function for two or
more formats), e.g:

If..Like "([0-9][0-9][0-9])*"

 

[jvoortman]

Thanks for the help. Unfortunately th code thing didn't
work. What do I do with the second "solution"?

-----Original Message-----
If all the telephone numbers are formatted similarly, something like
this will work:


Public Sub SpaceAboveTelephoneNumber()
Dim i As Long
Application.ScreenUpdating = False
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(i, 1).Value Like "[0-9][0-9][0-9]-*" Then _
Cells(i, 1).EntireRow.Insert
Next i
Application.ScreenUpdating = True
End Sub

Substitute for the format you want (or use an Or function for two or
more formats), e.g:

If..Like "([0-9][0-9][0-9])*"

I have some data running down column A. I need there to be
a space between the companies and their information. ome
companies show street address and city and postal code
while some only show the telephone number. I guess if
there was a way to get the code to put a blank cell above
everytime a cell occurs with a telephone number, the goal
would b accomplished. Any Ideas???? Thansk for any help
you can offer.

.

 

[JE McGimpsey]

What does "didn't work" mean? Did the code run? give an error? cause a
crash? do nothing?

The "code thing" implements your second proposed solution. The
alternative I proposed was for substituting a different format of
telephone number in the code.

 

[JE McGimpsey]

Your specification is still ambiguous - do you want to separate the
telephone number from the company name? If not, just modify my code
slightly:

If Cells(i, 1).Value Like "*[0-9][0-9][0-9]-*" Then _

but note that this doesn't really put "a space between the companies and
their information", rather putting a space between each company and
*other* company's information.

If not, what should the final format be?

 

[jvoortman]

thanks for your patience....
I ran the code and it did nothing. To further explain, I
wish to go from data like this:

BMS Sanitation & Safety 888-363-5376
BMS Sanitation & Safety 905-885-8904
CleanSense Solutions Inc 905-672-7936
7033 Telford Way,
Mississauga, ON
L5S 1V4
French G T Paper Limited 905-374-3434
4460 Montrose Road,
Niagara Falls, ON
L2H 1K2
Legere Industrial Supplies Ltd 613-829-8010
1120 Morrison Drive,
Ottawa, ON
K2H 8M7
Snelling Paper & Sanitation Ltd 613-745-7184
1410 Triole Street,
Ottawa, ON
K1B 3M5
Swish Gordon 519-659-2101
540 Admiral Drive,
London, ON
N5V 4L5

TO END UP LIKE THIS......

BMS Sanitation & Safety 888-363-5376

BMS Sanitation & Safety 905-885-8904

CleanSense Solutions Inc 905-672-7936
7033 Telford Way,
Mississauga, ON
L5S 1V4

French G T Paper Limited 905-374-3434
4460 Montrose Road,
Niagara Falls, ON
L2H 1K2

Legere Industrial Supplies Ltd 613-829-8010
1120 Morrison Drive,
Ottawa, ON
K2H 8M7

Snelling Paper & Sanitation Ltd 613-745-7184
1410 Triole Street,
Ottawa, ON
K1B 3M5

Swish Gordon 519-659-2101
540 Admiral Drive,
London, ON
N5V 4L5

 

[JE McGimpsey]

See my other reply.

 

[jvoortman]

well I ran the code, and it only placed a blank
cell "after" and not "before" as required in three places
out of 147 rows. Is there a possible error in the code?

 

[JE McGimpsey]

well I ran the code, and it only placed a blank
cell "after" and not "before" as required in three places
out of 147 rows. Is there a possible error in the code?

It would only put the blank cell after if the address also had the
pattern specified in the Like "..." statement. Change that pattern to
further refine the split. AFAICT, it worked perfectly on the example
data you gave.