Seperating Text from Numbers in a cell
 

Hi, Column C has a bunch of addresses that have no spaces between the
number and street. (27smith lane instead of 27 smith lane). Is there
a way for me to quickly seperate the number and street name from the
preceding number in each cell of column c? There are 1075 cells in
column C. Thanks!

Seperating Text from Numbers in a cell
 

hi,

=LEFT(A1LEN(A1-5)

if this helps please click yes thanks

"MaryJ" wrote:

Hi, Column C has a bunch of addresses that have no spaces between the
number and street. (27smith lane instead of 27 smith lane). Is there
a way for me to quickly seperate the number and street name from the
preceding number in each cell of column c? There are 1075 cells in
column C. Thanks!

Seperating Text from Numbers in a cell
 

the below only (sort of) works if the address has 5 characters for the street
and does not account for the number separation.

"Eduardo" wrote:

hi,

=LEFT(A1LEN(A1-5)

if this helps please click yes thanks

"MaryJ" wrote:

Hi, Column C has a bunch of addresses that have no spaces between the
number and street. (27smith lane instead of 27 smith lane). Is there
a way for me to quickly seperate the number and street name from the
preceding number in each cell of column c? There are 1075 cells in
column C. Thanks!

Seperating Text from Numbers in a cell
 

Opps there is a comma missing

=LEFT(A1,LEN(A1-5)

"Eduardo" wrote:

hi,

=LEFT(A1LEN(A1-5)

if this helps please click yes thanks

"MaryJ" wrote:

Hi, Column C has a bunch of addresses that have no spaces between the
number and street. (27smith lane instead of 27 smith lane). Is there
a way for me to quickly seperate the number and street name from the
preceding number in each cell of column c? There are 1075 cells in
column C. Thanks!

Seperating Text from Numbers in a cell
 

On Sep 1, 10:21*am, Eduardo wrote:
Opps there is a comma *missing

=LEFT(A1,LEN(A1-5)



"Eduardo" wrote:
hi,

=LEFT(A1LEN(A1-5)

if this helps please click yes thanks

"MaryJ" wrote:

Hi, Column C has a bunch of addresses that have no spaces between the
number and street. *(27smith lane instead of 27 smith lane). *Is there
a way for me to quickly seperate the number and street name from the
preceding number in each cell of column c? *There are 1075 cells in
column C. *Thanks!- Hide quoted text -

- Show quoted text -

Ok, I'm still kind of confused... there are different lengths of
numbers. Any chance I could get a little more help?

Seperating Text from Numbers in a cell
 

=LOOKUP(10^10,MID(A1,1,ROW(INDIRECT("1:"&LEN(A1))) )+0)


"MaryJ" wrote:

Hi, Column C has a bunch of addresses that have no spaces between the
number and street. (27smith lane instead of 27 smith lane). Is there
a way for me to quickly seperate the number and street name from the
preceding number in each cell of column c? There are 1075 cells in
column C. Thanks!

Seperating Text from Numbers in a cell
 

Well, that's even easier than a macro... And if needed, assuming the below
lookup formula is in A2:

=right(A1,len(a1)-len(A2)) will get you the street name in A3

"Teethless mama" wrote:

=LOOKUP(10^10,MID(A1,1,ROW(INDIRECT("1:"&LEN(A1))) )+0)


"MaryJ" wrote:

Hi, Column C has a bunch of addresses that have no spaces between the
number and street. (27smith lane instead of 27 smith lane). Is there
a way for me to quickly seperate the number and street name from the
preceding number in each cell of column c? There are 1075 cells in
column C. Thanks!

Seperating Text from Numbers in a cell
 

On Tue, 1 Sep 2009 09:29:01 -0700, Sean Timmons
wrote:

Well, that's even easier than a macro... And if needed, assuming the below
lookup formula is in A2:

=right(A1,len(a1)-len(A2)) will get you the street name in A3

With this method, it would be prudent to add some error-checking to ensure the
string starts with a number. There are plenty of addresses around here that
consist just of a street name.
--ron

Seperating Text from Numbers in a cell
 

.... and a parenthesis missing too?
--
David Biddulph

"Eduardo" wrote in message
...
Opps there is a comma missing

=LEFT(A1,LEN(A1-5)

"Eduardo" wrote:

hi,

=LEFT(A1LEN(A1-5)

if this helps please click yes thanks

"MaryJ" wrote:

Hi, Column C has a bunch of addresses that have no spaces between the
number and street. (27smith lane instead of 27 smith lane). Is there
a way for me to quickly seperate the number and street name from the
preceding number in each cell of column c? There are 1075 cells in
column C. Thanks!