Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I extract data into two cells?
Using Vista, Excel 2007
I have the following: In A1 - 'Bill Smith (2468)' In A2 - 'James Sinclair (659873)' and so on to A350, with names of various lengths and numbers containing a different number of digits in parenthesis. I require, in text format: In B1 - 'Bill Smith' In C1 '(2468)' In B2 - 'James Sinclair' In C2 '(659873)' and so on to row 350. Any advice will be appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I extract data into two cells?
one method:
=LEFT(A1,FIND("(",A1)-1) for names =MID(A1,FIND("(",A1)+1,LEN(A1)-FIND("(",A1)-1) for numbers should you need the numbers to be "numeric" not text then =MID(A1,FIND("(",A1)+1,LEN(A1)-FIND("(",A1)-1)*1 HIH On 17 Sty, 12:05, "Stan in South Africa" <me@there wrote: Using Vista, Excel 2007 I have the following: In A1 - 'Bill Smith (2468)' In A2 - 'James Sinclair (659873)' and so on to A350, with names of various lengths and numbers containing a different number of digits in parenthesis. I require, in text format: In B1 - 'Bill Smith' *In C1 '(2468)' In B2 - 'James Sinclair' In C2 '(659873)' and so on to row 350. Any advice will be appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I extract data into two cells?
Hi
Here's an other method. Use find/replace to replace "(" with "~(" Then select all cells and goto "Text to columns" on the Data tab Delemited Other : ~ Next Destination : $B$1 OK Regards, Per "Jarek Kujawa" skrev i meddelelsen ... one method: =LEFT(A1,FIND("(",A1)-1) for names =MID(A1,FIND("(",A1)+1,LEN(A1)-FIND("(",A1)-1) for numbers should you need the numbers to be "numeric" not text then =MID(A1,FIND("(",A1)+1,LEN(A1)-FIND("(",A1)-1)*1 HIH On 17 Sty, 12:05, "Stan in South Africa" <me@there wrote: Using Vista, Excel 2007 I have the following: In A1 - 'Bill Smith (2468)' In A2 - 'James Sinclair (659873)' and so on to A350, with names of various lengths and numbers containing a different number of digits in parenthesis. I require, in text format: In B1 - 'Bill Smith' In C1 '(2468)' In B2 - 'James Sinclair' In C2 '(659873)' and so on to row 350. Any advice will be appreciated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I extract data into two cells?
sorry - forgot the brackets
=MID(A1,FIND("(",A1),LEN(A1)-FIND("(",A1)+1) for numbers is correct On 17 Sty, 12:27, Jarek Kujawa wrote: one method: =LEFT(A1,FIND("(",A1)-1) for names =MID(A1,FIND("(",A1)+1,LEN(A1)-FIND("(",A1)-1) for numbers should you need the numbers to be "numeric" not text then =MID(A1,FIND("(",A1)+1,LEN(A1)-FIND("(",A1)-1)*1 HIH On 17 Sty, 12:05, "Stan in South Africa" <me@there wrote: Using Vista, Excel 2007 I have the following: In A1 - 'Bill Smith (2468)' In A2 - 'James Sinclair (659873)' and so on to A350, with names of various lengths and numbers containing a different number of digits in parenthesis. I require, in text format: In B1 - 'Bill Smith' *In C1 '(2468)' In B2 - 'James Sinclair' In C2 '(659873)' and so on to row 350. Any advice will be appreciated.- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I extract data into two cells?
One more.
Copy the all the data into two columns (identical columns when you're done). Select the first column ctrl-h (replace) what: _(* (space character, open paren, asterisk) with: (leave blank) replace all This will remove the (####) from the first column. Select the second column ctrl-h (replace) what: *( (open paren, asterisk) with: (leave blank) replace all This will remove the name. Select the second column ctrl-h (replace) what: ) (close paren) with: (leave blank) replace all This will remove the closing paren Stan in South Africa wrote: Using Vista, Excel 2007 I have the following: In A1 - 'Bill Smith (2468)' In A2 - 'James Sinclair (659873)' and so on to A350, with names of various lengths and numbers containing a different number of digits in parenthesis. I require, in text format: In B1 - 'Bill Smith' In C1 '(2468)' In B2 - 'James Sinclair' In C2 '(659873)' and so on to row 350. Any advice will be appreciated. -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I extract data into two cells?
excellent!
;-) On 17 Sty, 14:24, Dave Peterson wrote: One more. Copy the all the data into two columns (identical columns when you're done). Select the first column ctrl-h (replace) what: *_(* * * * * * * * (space character, open paren, asterisk) with: *(leave blank) replace all This will remove the (####) from the first column. Select the second column ctrl-h (replace) what: **( * * * * * * *(open paren, asterisk) with: *(leave blank) replace all This will remove the name. Select the second column ctrl-h (replace) what: *) * * * * * * *(close paren) with: *(leave blank) replace all This will remove the closing paren Stan in South Africa wrote: Using Vista, Excel 2007 I have the following: In A1 - 'Bill Smith (2468)' In A2 - 'James Sinclair (659873)' and so on to A350, with names of various lengths and numbers containing a different number of digits in parenthesis. I require, in text format: In B1 - 'Bill Smith' *In C1 '(2468)' In B2 - 'James Sinclair' In C2 '(659873)' and so on to row 350. Any advice will be appreciated. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to extract just text from cells | Excel Worksheet Functions | |||
Tying to extract all data from a pivot pulling from external data | Excel Discussion (Misc queries) | |||
Extract numbers from cells | New Users to Excel | |||
extract data from a range of cells in rows or columns when a date. | Excel Worksheet Functions | |||
extract data from cells | Excel Worksheet Functions |