How to Replace numbers and text with numbers only?

[Guest]

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.

 

[Govind]

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

 

[Paul Corrado]

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

 

[Guest]

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]

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

 

[Guest]

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?

 

[NHarkawat]

For numeric portion use
=--LEFT(A2,SUMPRODUCT(--ISNUMBER(-LEFT(A2,seq))))
for text portion use
=MID(A2,SUMPRODUCT(--ISNUMBER(-LEFT(A2,seq)))+1,255)

where named range
Seq =ROW(INDIRECT("1:1024"))

 

[Paul Corrado]

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(LEFT(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))

 

[Guest]

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]

Select the cell then use

Copy/Paste Special and select Value

to copy the formula result over the formula. (If it is the entire column,
just highlight the whole column.)

Also, I should note that the formula posted by NHarkawat was a bit better
than mine. But I'm glad you were able to use what I gave you.

 

[Guest]

Paul:
One more question, please. I now have a column where some of the cells have
a space as the first character. So, now I need a formula that will eliminate
the blank space, just leaving the rest of the text in the cell. I will
appreciate advice.

 

[Paul Corrado]

=TRIM(A1)

Should do the trick

Paul:
One more question, please. I now have a column where some of the cells have
a space as the first character. So, now I need a formula that will eliminate
the blank space, just leaving the rest of the text in the cell. I will
appreciate advice.