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 phone number.

As the end result I'd like to have YWCA in column A. The account number and only the account number in Column B. I'd like to delete the phone 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.

[Max]

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 B1:D1, 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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jonhunt" wrote:
> 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 phone
> number.
>
> As the end result I'd like to have YWCA in column A. The account number
> and only the account number in Column B. I'd like to delete the phone
> 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.
>
>
> --
> jonhunt
> ------------------------------------------------------------------------
> jonhunt's Profile: http://www.excelforum.com/member.php...o&userid=35370
> View this thread: http://www.excelforum.com/showthread...hreadid=551403
>
>

[Max]

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

[jonhunt]

Mas,

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. But what do you mean by "freeze the values....then 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 Paste Special and Values and select OKAY. (You are basically replacing the contents with the exact contents but without references.
5. Now select your entire range of filled cells. Go to Data and Sort by Column A.
6. Now you can easily select the range of cells in Column A that have only numbers and deleted those. You can select the range of cell sin Column B that have only names and delete those. You are left with Column A showing the account names and Column B showing account numbers and phone numbers.
7. Now Select Column B and go to select the entire column and go to Data and select Convert Text to Columns, then select fixed width. Move the vertical line that separates the area code from the phone number to the right of the phone number. Select Next and Finish and say yes to the question of replacing contents.

[Max]

"jonhunt" wrote:
> 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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[jonhunt]

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

[Max]

"jonhunt" wrote:
> Thanks...I was essentially doing that, but I just didn't understand the
> terminology. I really appreciate the help.

You're welcome !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---