Fomula to copy every third cell in col

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PPG - 22 Sep 2006 02:27 GMT

I have a list of business addresses and phone numbers in 1 column. Ideally I
would like Col A the be name. Col B address and c phone number. Here is the
format I have right now. Please help.

Ultimate Staffing
3435 W Cheyenne Ave, North Las Vegas, NV
(702) 647-0477 - 2.8 mi SE
Allied Forces
1401 N Decatur Blvd # 21, Las Vegas, NV
(702) 647-6668 - 4.3 mi S
Labor Express
1401 N Decatur Blvd # 21, Las Vegas, NV
(702) 647-6668 - 4.3 mi S
Workers
1401 N Decatur Blvd, Las Vegas, NV
(702) 647-6668 - 4.3 mi S
Flexible Staffing
800 N Rainbow Blvd, Las Vegas, NV
(702) 948-5030 - 4.8 mi S
On Assignment Healthcare Staffing
500 N Rainbow Blvd, Las Vegas, NV
(702) 369-4150 - 5.0 mi S
Eastridge Temps
4310 Losee Rd # 3, North Las Vegas, NV
(702) 633-7800 - 5.8 mi E
Mercer Staffing
2575 E Craig Rd, North Las Vegas, NV
(702) 633-6800 - 5.9 mi E
Corestaff
2707 E Craig Rd, North Las Vegas, NV
(702) 639-6463 - 6.0 mi E
Workers
6126 W Charleston Blvd, Las Vegas, NV
(702) 647-9434 - 6.1 mi S

Reply to this Message

Max - 22 Sep 2006 02:40 GMT

One way ..
Assuming source data is running in A1 down
Put in B1:
=INDEX($A:$A,ROW(A1)*3-3+COLUMN(A1))
Copy B1 to D1, then just fill down until zeros appear, signalling exhaustion
of data extract from col A. Freeze the values in cols B to D with an
"in-place" Copy > Paste special as values. Delete col A, and you should have
exactly what you wanted.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> I have a list of business addresses and phone numbers in 1 column. Ideally I
> would like Col A the be name. Col B address and c phone number. Here is the
[quoted text clipped - 30 lines]
> 6126 W Charleston Blvd, Las Vegas, NV
> (702) 647-9434 - 6.1 mi S

Reply to this Message

Max - 22 Sep 2006 06:20 GMT

Oops, think I missed the additional splitting of the contents in col D
earlier to take out the tel no .. Here's the revised steps:

Assuming source data is running in A1 down

Put in B1:
=INDEX($A:$A,ROW(A1)*3-3+COLUMN(A1))
Copy B1 to D1

Put in E1:
=LEFT(D1,SEARCH("-",D1,SEARCH("-",D1)+1)-1)

Put in F1:
=MID(D1,SEARCH("-",D1,SEARCH("-",D1)+1)+1,99)

Select B1:F1, fill down until zeros appear in cols B to D, signalling
exhaustion of data extract from col A. Freeze the values in cols B to F with
an "in-place" Copy > Paste special as values. Delete the source col A and col
D. That should return things pretty much the way you want in cols A to C.

Note: I'm not sure what the data in the last col D (eg: 2.8 mi SE) means,
but if it's a piece of the address to be concat with the main address in col
B, then just put this in say E1: =TRIM(B1&" "&D1) and copy down. Then just
copy col E and overwrite col B with a paste special as values.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message