Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Robert Judge
 
Posts: n/a
Default How to Replace numbers and text with numbers only?

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.










  #2   Report Post  
Paul Corrado
 
Posts: n/a
Default

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.






"Robert Judge" wrote in message
...
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.












  #3   Report Post  
Robert Judge
 
Posts: n/a
Default

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" wrote:

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.


  #4   Report Post  
Paul Corrado
 
Posts: n/a
Default


=TRIM(A1)

Should do the trick




"Robert Judge" wrote in message
...
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" wrote:

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.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
excel - numbers as text Thuferhawat New Users to Excel 12 January 24th 05 10:29 PM
converting numbers to text and prefill text field with 0's Jan Buckley Excel Discussion (Misc queries) 2 January 20th 05 10:03 PM
Convert text to numbers gennario Excel Discussion (Misc queries) 6 January 11th 05 12:56 AM
Sorting when some numbers have a text suffix confused on the tundra Excel Discussion (Misc queries) 5 December 18th 04 11:19 PM
I enter numbers and they are stored as text burkeville Excel Discussion (Misc queries) 5 December 3rd 04 02:59 AM


All times are GMT +1. The time now is 12:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"