Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
jonhunt
 
Posts: n/a
Default Moving cell data and separating or deleting


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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default Moving cell data and separating or deleting

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


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default Moving cell data and separating or deleting

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
---
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
jonhunt
 
Posts: n/a
Default Moving cell data and separating or deleting


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.


--
jonhunt
------------------------------------------------------------------------
jonhunt's Profile: http://www.excelforum.com/member.php...o&userid=35370
View this thread: http://www.excelforum.com/showthread...hreadid=551403

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default Moving cell data and separating or deleting

"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
---


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
jonhunt
 
Posts: n/a
Default Moving cell data and separating or deleting


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


--
jonhunt
------------------------------------------------------------------------
jonhunt's Profile: http://www.excelforum.com/member.php...o&userid=35370
View this thread: http://www.excelforum.com/showthread...hreadid=551403

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default Moving cell data and separating or deleting

"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
---
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Separating text and data in a column Amber Excel Worksheet Functions 1 April 4th 06 11:42 PM
coma not separating data moinik123 Excel Discussion (Misc queries) 3 March 28th 06 06:21 AM
Separating information in one cell into several cells ChuckW Excel Discussion (Misc queries) 5 February 14th 06 03:22 PM
Separating data in a cell chellegar Excel Discussion (Misc queries) 4 August 16th 05 08:57 PM
Separating data ( 1 time range) in one column Steve Excel Worksheet Functions 2 January 25th 05 08:59 PM


All times are GMT +1. The time now is 03:45 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"