Moving cell data and separating or deleting

[jonhunt]

In cell A1 I have YWCA. In cell A2 I have 012345 and (101) 452 1234.
These are respectively an account name, account number and phon
number.

As the end result I'd like to have YWCA in column A. The account numbe
and only the account number in Column B. I'd like to delete the phon
number.

The data isn't all in a neat row..Here's a sample:
YWCA
0584377 (9 04) 727

YWCA OF JAX/COMMUNITY CON
0584378 (9 04) 35 0-

ZABATT, INC
05873 03 (9 04) 384-

ZAPA MANAGEMENT INC/ADULT
0588299 (9 04) 42

ZAXBY'S C/O ST JOHN & PAR
05883 05 (9 04) 281

ZENITH MEDIA/NESTLE
0588531 (212) 85

ZZ MAC, INC
05872 02 (9 04) 46
Any help would be appreciated

 

[Guest]

Perhaps one play to try ..

Assuming data as posted is representative and is running in A1 down

Put in B1: =INDEX($A:$A,ROW(A1)*3-3+COLUMN(A1))
Copy B1 to C1
Put in D1: =SUBSTITUTE(LEFT(TRIM(C1),SEARCH("(",TRIM(C1))-1)," ","")
Select B11, fill down until zeros appear in cols B & C, signalling
exhaustion of data. The above will re-arrange data in col A into cols B and
C, and with col D extracting the account numbers, as required. I used
SUBSTITUTE in col D to "clean up" what I thought were inconsistencies in the
account numbers, viz. removal of any extra spaces in the string before the
left parens "(".

Freeze the values by selecting cols B to D, then do an "in-place":
Copy > Paste special > Check "Values" > OK
Then just delete col C, and you'd get the final results:
Account names in col B, account numbers in col C

 

[Guest]

A sample implementation is available at:
http://www.savefile.com/files/2234247
ReArranging col data into 2 cols n extract acc nos.xls

---

 

[jonhunt]

Mas,

Thanks. I'd been working on this after posting the thread and foun
another way to make it work...posted below, but I followed your exampl
and it works great. But what do you mean by "freeze the values....the
do an in place.?"

Thanks
1. Insert a new column B
2. In B1 write: =a2
3. Select B1 and fill the remainder of B
4. Select Column B and go to Edit and select Copy, then select Past
Special and Values and select OKAY. (You are basically replacing th
contents with the exact contents but without references.
5. Now select your entire range of filled cells. Go to Data and Sort b
Column A.
6. Now you can easily select the range of cells in Column A that hav
only numbers and deleted those. You can select the range of cell si
Column B that have only names and delete those. You are left wit
Column A showing the account names and Column B showing account number
and phone numbers.
7. Now Select Column B and go to select the entire column and go t
Data and select Convert Text to Columns, then select fixed width. Mov
the vertical line that separates the area code from the phone number t
the right of the phone number. Select Next and Finish and say yes to th
question of replacing contents

 

[Guest]

Thanks. I'd been working on this after posting the thread and found
another way to make it work...posted below, but I followed your example
and it works great.

Good to hear you got it worked out as well.
Thanks for posting back & sharing your findings with us.

But what do you mean by "freeze the values....then do an in place.?"

Freezing the values returned by the formulas in cols B to D means to kill /
remove the formulas while leaving the evaluated values behind. And one way to
do this would be to copy cols B to D, then do a paste special > values over
"itself" (ie. an "in-place" paste special over cols B to D). The freezing as
values would allow us to then clean up & delete any unwanted cols, eg: delete
cols A and C in the example set-up, so as to yield the final product.

---

 

[jonhunt]

Thanks...I was essentially doing that, but I just didn't understand th
terminology. I really appreciate the help

 

[Guest]

Thanks...I was essentially doing that, but I just didn't understand the
terminology. I really appreciate the help.

You're welcome !

---