Problem with figuring this out?

People will be copying and pasting 4 columns. One of the columns will have an amount and then 2 letters after it like this EXAMPLE: 1000 NP . My question is I need to sum the entire column but the NP needs to be eliminated somehow. Is there a way to somehow make a secondary column to take the number part of the pasted cell and not the "NP" part and then in that secondary column then it would be easy to sum it?

Example:

Date Item User Amount
06/14/06 cheese widow 1000 NP <---Sum this

But somehow get rid of the NP thats there or make a 5th column that will list the 4th columns number but not the "NP"

any suggestions?

Originally Posted by widow

People will be copying and pasting 4 columns. One of the columns will have an amount and then 2 letters after it like this EXAMPLE: 1000 NP . My question is I need to sum the entire column but the NP needs to be eliminated somehow. Is there a way to somehow make a secondary column to take the number part of the pasted cell and not the "NP" part and then in that secondary column then it would be easy to sum it?

Example:

Date Item User Amount
06/14/06 cheese widow 1000 NP <---Sum this

But somehow get rid of the NP thats there or make a 5th column that will list the 4th columns number but not the "NP"

any suggestions?

is there always a space between the text and numbers? if so,

Please Login or Register  to view this content.

or

=VALUE(SUBSTITUTE(D1," NP",""))

If NP is constant

VBA Noob

Or

again if NP is static/constant

Select column. Press Ctrl + H. In the find box enter NP and leave the replace box empty and then select replace all

VBA Noob