Remove dash(s)

[Pierre]

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

 

[Guest]

Hi Pierre,

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

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

 

[Guest]

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

The will preserve the last dash if it is followed by a single digit.

 

[Guest]

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,"-",""))

 

[Roger Govier]

Hi Pierre

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

 

[Beege]

Pierre

I got this to work...

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

Beege

 

[Pierre]

Hi Pierre

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

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

Pierre

 

[Ron Rosenfeld]

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

 

[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

 

[Ron Rosenfeld]

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