Paul:
Great. That worked. Thanks. Now, I'm hoping you can help me with what I
think would be the last step. I now have the long complicated formula you
gave me in one column, that has produced the desire effect, for example the
number "104". Can I NOW convert the cell contents so that rather than
showing the complicated formula, it now will just show the "104"? In order
words, now that the forumula has done its job, can I strip the cell of the
forumula contents, and replace it with the result itself ("104") rather than
the long formula ("=LEFT(A1,VLOOKUP...ETC.) you gave me? I will appreciate
advice.

"Paul Corrado" wrote:

This will extract the First Number for any number with 5 or less digits
(Provided there is no leading space)

=LEFT(A1,VLOOKUP(SUMPRODUCT((ISERROR(VALUE(MID(LEF T(A1,5),{1;2;3;4;5},1))))*
({1;2;3;4;5})),{14,1;12,2;9,3;5,4;0,5},2,FALSE))


With the above formula in B1, use to get the remainder of the address

=RIGHT(A1,LEN(A1)-LEN(B1))





"Robert Judge" wrote in message
...
Paul:
That did it! Thanks. But now, another issue:

Some of the addresses are, for instance, "123 Main St." but other
addresses
are "345South St.", that is, there is no space between the number and the
street. So, I need another forumula before I can apply the first one.
That
is, a formula that will find an entry like "567North St." and change it to
"567 North St." Can you help with that one?

"Paul Corrado" wrote:

Robert

Make sure the cell references are correct.

Otherwise, copy & paste one of your data cells into a message. Possibly
the
form is not exactly as you have described


"Robert Judge" wrote in message
...
Paul:
I copied the two formulas into two columns. However, each produced
the
error "#VALUE!" So, something is missing. Can you elaborate at all?

"Paul Corrado" wrote:

A slight modification may help

For the Number
=LEFT(A1,FIND(" ",A1)-1)

For the rest of the address
=RIGHT(A1,LEN(A1)-FIND(" ",A1))



"Govind" wrote in message
...
Hi,

Try using Data-Text to columns and split the column.

Or else if the numbers are always 3 digits from the left, then add
another column and use the formula = LEFT(A1,3) (assuming the data
is
in
column A)for having the numbers alone.

For addresses use =right(A1,len(A1)-4)

Regards

Govind

Robert Judge wrote:
I am using Excel 2003. I have a worksheet that has a column
with
addresses
in the format of "123 Main Street" "345 South Street", etc. I
want
to
break
that data into two columns, one column with the number ("123")
and
one
column
with the street ("Main St.). Any suggestions? I will
appreciate
advice.