Remove dash(s)

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

Hi Pierre,

Let's say that one of your data items is in cell A1. Set an open adjacent
cell to:

=SUBSTITUTE(A1,"-","")

"Pierre" 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
>
>

=SUBSTITUTE(LEFT(A1,LEN(A1)-2),"-","") & RIGHT(A1,2)

The will preserve the last dash if it is followed by a single digit.
--
Gary's Student


"Pierre" 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
>
>

Whoops sorry, ignore my previous post, here's what you want:
=IF(MID(A6,LEN(A6)-1,1)="-",LEFT(SUBSTITUTE(A6,"-",""),LEN(SUBSTITUTE(A6,"-",""))-1)&"-"&RIGHT(SUBSTITUTE(A6,"-",""),1),SUBSTITUTE(A6,"-",""))

"Pierre" 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
>
>

Hi Pierre

Try
=IF(MID(RIGHT(A1,3),2,1)="-",
SUBSTITUTE(LEFT(A1,LEN(A1)-3),"-","")&RIGHT(A1,3),
SUBSTITUTE(A1,"-",""))

--
Regards

Roger Govier


"Pierre" <[email protected]> wrote in message
news:[email protected]...
> 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
>

Pierre

I got this to work...

=IF(SEARCH("-",A1,1)=LEN(A1)-2,SUBSTITUTE(A1,"-",""),A1)

Beege

"Pierre" <[email protected]> wrote in message
news:[email protected]...
> 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
>

Roger Govier wrote:
> Hi Pierre
>
> Try
> =IF(MID(RIGHT(A1,3),2,1)="-",
> SUBSTITUTE(LEFT(A1,LEN(A1)-3),"-","")&RIGHT(A1,3),
> SUBSTITUTE(A1,"-",""))
>
> --
> Regards
>
> Roger Govier
>
>


The suggested answers worked like a charm! Thanks to those who
responded.

Pierre

On 14 Jun 2006 08:46:17 -0700, "Pierre" <[email protected]> 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 Rosenfeld wrote:
> On 14 Jun 2006 08:46:17 -0700, "Pierre" <[email protected]> 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

On 14 Jun 2006 17:25:05 -0700, "Pierre" <[email protected]> wrote:

>
>Ron Rosenfeld wrote:
>> On 14 Jun 2006 08:46:17 -0700, "Pierre" <[email protected]> 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

You're welcome.

There are many other very useful functions in that add-in.
--ron