Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separating text and data in a column | Excel Worksheet Functions | |||
coma not separating data | Excel Discussion (Misc queries) | |||
Separating information in one cell into several cells | Excel Discussion (Misc queries) | |||
Separating data in a cell | Excel Discussion (Misc queries) | |||
Separating data ( 1 time range) in one column | Excel Worksheet Functions |