Find/Repalce
 

I have a file in which cells have text followed by numbers...the ending
numbers are always 4 digits....anytime the number is greater than 2550, I
want to replace it with 2550, is there a way to generate a formula or a macro
that can do that but not mess with the text before it in the cell?

Thanks!

Find/Repalce
 

The number will always have a "Z-" before it, so that may be able to be an
indicator...however if it is "X-" OR "Y-" I do not want the number changed.

"lightbulb" wrote:

I have a file in which cells have text followed by numbers...the ending
numbers are always 4 digits....anytime the number is greater than 2550, I
want to replace it with 2550, is there a way to generate a formula or a macro
that can do that but not mess with the text before it in the cell?

Thanks!

Find/Repalce
 

With your data in cell A1; try the below formula

=IF(ISERROR(SEARCH("Z-",A1)),A1,LEFT(A1,SEARCH("Z-",A1)+1) &
MIN(2550,MID(A1,SEARCH("Z-",A1)+2,5)))
(all in one line)

If this post helps click Yes
---------------
Jacob Skaria


"lightbulb" wrote:

The number will always have a "Z-" before it, so that may be able to be an
indicator...however if it is "X-" OR "Y-" I do not want the number changed.

"lightbulb" wrote:

I have a file in which cells have text followed by numbers...the ending
numbers are always 4 digits....anytime the number is greater than 2550, I
want to replace it with 2550, is there a way to generate a formula or a macro
that can do that but not mess with the text before it in the cell?

Thanks!