How do I extract data into two cells?

[Stan in South Africa]

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.

 

[Jarek Kujawa]

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

 

[Per Jessen]

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" <[email protected]> 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

 

[Jarek Kujawa]

sorry - forgot the brackets

=MID(A1,FIND("(",A1),LEN(A1)-FIND("(",A1)+1) for numbers

is correct

 

[Dave Peterson]

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

 

[Jarek Kujawa]

excellent!
;-)

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: