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.

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.

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.

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 -

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

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