Ron Rosenfeld wrote:
> On 14 Jun 2006 08:46:17 -0700, "Pierre" <cowguy@aol.com> wrote:
>
> >Maybe someone here could provide guidance:
> >
> >Have a column containing data which may have 1-3 dashes locates within
> >each cell value.
> >Such as:
> >125-457A
> >158-857-1116
> >124184A-1
> >125-8745-22D9
> >458-2145-002-7
> >
> >
> >Would like to remove all the dashes "unless" the last dash to the right
> >is followed by a just a single digit. The results would be as follows:
> >125457A
> >1588571116
> >124184A-1
> >125874522D9
> >4582145002-7
> >
> >On the example data, it kept 2 of the dashes in the cell values,
> >because the were only one digit away from the extreme right, but
> >removed all others.
> >
> >TIA for any thoughts.
> >
> >Pierre
>
> The previously posted solutions (except the first) work on your posted data.
> However, they do not meet the requirements you stated which was to remove all
> the dashes unless the last dash is followed by just a single DIGIT.
>
> The previous solutions will not remove the last dash if it is followed by just
> a single CHARACTER (not restricted to just a digit.
>
> In other words, with data:
>
> 125-457-A --> 125457-A
>
> If that is what you want, fine. But since the last character is not a digit, I
> would have expected that you would have wanted all of the dashes removed.
>
> To comply with your stated requirements, you could use a regular expression.
>
> Download and install Longre's free morefunc.xll add-in from:
> http://xcell05.free.fr
>
> Then use the formula:
>
> =REGEX.SUBSTITUTE(A1,"-(?!\d$)")
>
> which says remove all dashes that are NOT followed by a single digit that
> terminates the string.
>
> If there could be trailing spaces, or other non-printing characters that you
> wish to ignore, you could use:
>
> =REGEX.SUBSTITUTE(A1,"-(?!\d\s*$)")
>
>
> --ron

Ron,
I appreciate the extra effort you've put forth. I guess I just got
lucky, in that none of the cells contents end with a character. Thanks
for the lead on the add-in as well.

Pierre