function please

[JG]

little help, thanks


I have a column with street addresses, the street numbers are
separated by one space from the street name.
I would like to cut the numbers (1 to maybe a high of 1900) and paste
the street numbers to an adjoining column.

I'll have to start reading Excel books. any recommendations. thanks
again

[[email protected]]

JG:

Try this formula in the uppermost cell of the column you want the #'s
to appear in and just autofill it down the column. =LEFT(A1,FIND("
",A1)-1)

[Anne Troy]

Try Daily Dose:
http://www.*****-blog.com/archives/2...rmulas-part-1/
************
Anne Troy
www.OfficeArticles.com

"JG" <[email protected]> wrote in message
news:[email protected]...
> little help, thanks
>
>
> I have a column with street addresses, the street numbers are
> separated by one space from the street name.
> I would like to cut the numbers (1 to maybe a high of 1900) and paste
> the street numbers to an adjoining column.
>
> I'll have to start reading Excel books. any recommendations. thanks
> again

[Bob Phillips]

Didn't you ask this question yesterday and get a response?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JG" <[email protected]> wrote in message
news:[email protected]...
> little help, thanks
>
>
> I have a column with street addresses, the street numbers are
> separated by one space from the street name.
> I would like to cut the numbers (1 to maybe a high of 1900) and paste
> the street numbers to an adjoining column.
>
> I'll have to start reading Excel books. any recommendations. thanks
> again

[JG]

On Tue, 18 Oct 2005 00:15:22 +0100, "Bob Phillips"
<[email protected]> wrote:

>Didn't you ask this question yesterday and get a response?

Yes bob, i asked but not in this ng. it was m.p.e.

not very good at this. your reply was

=LEFT(A2,FIND(" ",a2)-1)

A2, i assume is the cell? tried that.


what goes between the quotation marks?

anyway i tried it, could get it to work

[Anne Troy]

Nothing goes between the quotes. What the formula is doing is finding all
the characters before a space. The " " represents the space.
************
Anne Troy
www.OfficeArticles.com

"JG" <[email protected]> wrote in message
news:[email protected]...
> On Tue, 18 Oct 2005 00:15:22 +0100, "Bob Phillips"
> <[email protected]> wrote:
>
>>Didn't you ask this question yesterday and get a response?
>
> Yes bob, i asked but not in this ng. it was m.p.e.
>
> not very good at this. your reply was
>
> =LEFT(A2,FIND(" ",a2)-1)
>
> A2, i assume is the cell? tried that.
>
>
> what goes between the quotation marks?
>
> anyway i tried it, could get it to work

[JG]

On Mon, 17 Oct 2005 21:14:37 -0400, "Anne Troy"
<[email protected]> wrote:

>Nothing goes between the quotes. What the formula is doing is finding all
>the characters before a space. The " " represents the space.
>************
>Anne Troy
>www.OfficeArticles.com
>
Anne this is what i have.

A1 and C1 are emty. i would like to move the numbers to column A
thanks

>

A B C
1 640 River Rd

2 628 River Rd

3 622 River Rd

[Roger Govier]

Hi

Make a copy of your file before you start in case things go wrong.

In cell A1 enter
=LEFT(B1,FIND(" ",B1)-1)

In cell C1 enter
=MID(B1,FIND(" ",B1)+1,255)

Copy the formulae down columns B and C as far as required.
Then, Copy the whole of column A, select cell A1, Paste Special>Values.
Copy column C, select cell C1, Paste Special>Values.

You can then delete column B, and you will have house numbers in A, and
Street names in B.



Regards

Roger Govier


JG wrote:
> On Mon, 17 Oct 2005 21:14:37 -0400, "Anne Troy"
> <[email protected]> wrote:
>
>
>>Nothing goes between the quotes. What the formula is doing is finding all
>>the characters before a space. The " " represents the space.
>>************
>>Anne Troy
>>www.OfficeArticles.com
>>
>
> Anne this is what i have.
>
> A1 and C1 are emty. i would like to move the numbers to column A
> thanks
>
>
>
> A B C
> 1 640 River Rd
>
> 2 628 River Rd
>
> 3 622 River Rd
>

[JG]

Thanks All

The formula was correct. I was not putting a space between the
quotation marks. Reading from the monitor, I couldn't tell; when I
printed out the reply, I noticed it.
thanks again