Separating Numerical and Alpha Data in cells
 

Does anyone know of a formula (or combination of formulas) that I can use to
split cells containing combined numerical and alpha datainto 2 separate cells.

Sample Data:
123 Anywhere Dr
10 Somewhere Dr
4998 Somewhere Else Ln

I have a listing of addresses and I want to split the number part of the
address and the alpha part of the address into 2 columns for sorting
purposes. I want to sort results by street name then by number. As you can
see above, the length of the numerical part of the address can vary, it is
not fixed in length.

Thanks,
Larry

The length of the nubers can be different as show above.

Separating Numerical and Alpha Data in cells
 

If it always separated from the text by a blank and is always at the
beginning of the string:

=LEFT(A1,FIND(" ",A1)-1)

"Larry" wrote:

Does anyone know of a formula (or combination of formulas) that I can use to
split cells containing combined numerical and alpha datainto 2 separate cells.

Sample Data:
123 Anywhere Dr
10 Somewhere Dr
4998 Somewhere Else Ln

I have a listing of addresses and I want to split the number part of the
address and the alpha part of the address into 2 columns for sorting
purposes. I want to sort results by street name then by number. As you can
see above, the length of the numerical part of the address can vary, it is
not fixed in length.

Thanks,
Larry

The length of the nubers can be different as show above.

Separating Numerical and Alpha Data in cells
 

...... for remainder of address:

=MID(A1,FIND(" ",A1)+1,255)

"Larry" wrote:

Does anyone know of a formula (or combination of formulas) that I can use to
split cells containing combined numerical and alpha datainto 2 separate cells.

Sample Data:
123 Anywhere Dr
10 Somewhere Dr
4998 Somewhere Else Ln

I have a listing of addresses and I want to split the number part of the
address and the alpha part of the address into 2 columns for sorting
purposes. I want to sort results by street name then by number. As you can
see above, the length of the numerical part of the address can vary, it is
not fixed in length.

Thanks,
Larry

The length of the nubers can be different as show above.

Separating Numerical and Alpha Data in cells
 

You can use Data | Text-to-columns to split the data at each space.
Although this will give you 3 columns for your first two examples and
4 for the third example, it is easy enough to re-combine these columns
with:

=TRIM(C2&" "&D2&" "&E2)

copied down the column and then fix the values. Alternatively, you
could use a formula with LEFT and RIGHT in conjunction with SEARCH or
FIND looking for the position of the first space.

Hope this helps.

Pete

On Jun 25, 3:50 pm, Larry wrote:
Does anyone know of a formula (or combination of formulas) that I can use to
split cells containing combined numerical and alpha datainto 2 separate cells.

Sample Data:
123 Anywhere Dr
10 Somewhere Dr
4998 Somewhere Else Ln

I have a listing of addresses and I want to split the number part of the
address and the alpha part of the address into 2 columns for sorting
purposes. I want to sort results by street name then by number. As you can
see above, the length of the numerical part of the address can vary, it is
not fixed in length.

Thanks,
Larry

The length of the nubers can be different as show above.

Separating Numerical and Alpha Data in cells
 

To split the string at the first space, try
=LEFT(A1,FIND(" ",A1)-1)
and
=RIGHT(A1,LEN(A1)-FIND(" ",A1))

You'll get an error if there's no space in the string, but at a push you
could trap for that.
--
David Biddulph

"Larry" wrote in message
...
Does anyone know of a formula (or combination of formulas) that I can use
to
split cells containing combined numerical and alpha datainto 2 separate
cells.

Sample Data:
123 Anywhere Dr
10 Somewhere Dr
4998 Somewhere Else Ln

I have a listing of addresses and I want to split the number part of the
address and the alpha part of the address into 2 columns for sorting
purposes. I want to sort results by street name then by number. As you
can
see above, the length of the numerical part of the address can vary, it is
not fixed in length.

Thanks,
Larry

The length of the nubers can be different as show above.

Separating Numerical and Alpha Data in cells
 

On Mon, 25 Jun 2007 07:50:00 -0700, Larry
wrote:

Does anyone know of a formula (or combination of formulas) that I can use to
split cells containing combined numerical and alpha datainto 2 separate cells.

Sample Data:
123 Anywhere Dr
10 Somewhere Dr
4998 Somewhere Else Ln

I have a listing of addresses and I want to split the number part of the
address and the alpha part of the address into 2 columns for sorting
purposes. I want to sort results by street name then by number. As you can
see above, the length of the numerical part of the address can vary, it is
not fixed in length.

Thanks,
Larry

The length of the nubers can be different as show above.

A2: 123 Anywhere St
B2:
=IF(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"012345678 9"))LEN(A2),
"",LOOKUP(1E+307,--MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},
A2&"0123456789")),ROW(INDIRECT("1:"&LEN(A2))))))

C2: =TRIM(SUBSTITUTE(A2,B2,"",1))


--ron

Separating Numerical and Alpha Data in cells
 

Thanks all. I ended up with a combination of LEFT, FIND and MID funbctions
that seem to be working for my purposes OK.

"Toppers" wrote:

If it always separated from the text by a blank and is always at the
beginning of the string:

=LEFT(A1,FIND(" ",A1)-1)

"Larry" wrote:

Does anyone know of a formula (or combination of formulas) that I can use to
split cells containing combined numerical and alpha datainto 2 separate cells.

Sample Data:
123 Anywhere Dr
10 Somewhere Dr
4998 Somewhere Else Ln

I have a listing of addresses and I want to split the number part of the
address and the alpha part of the address into 2 columns for sorting
purposes. I want to sort results by street name then by number. As you can
see above, the length of the numerical part of the address can vary, it is
not fixed in length.

Thanks,
Larry

The length of the nubers can be different as show above.