function please

J

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
 
T

twox4s

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)
 
B

Bob Phillips

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

--

HTH

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

JG

Didn't you ask this question yesterday and get a response?
Click to expand...

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
 
J

JG

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
Click to expand...
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
 
R

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
 
J

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
 
Top