Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove dash(s)
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove dash(s)
=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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove dash(s)
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove dash(s)
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" wrote in message ups.com... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove dash(s)
Pierre
I got this to work... =IF(SEARCH("-",A1,1)=LEN(A1)-2,SUBSTITUTE(A1,"-",""),A1) Beege "Pierre" wrote in message ups.com... 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove dash(s)
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove dash(s)
On 14 Jun 2006 08:46:17 -0700, "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 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove dash(s)
Ron Rosenfeld wrote: On 14 Jun 2006 08:46:17 -0700, "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 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove dash(s)
On 14 Jun 2006 17:25:05 -0700, "Pierre" wrote:
Ron Rosenfeld wrote: On 14 Jun 2006 08:46:17 -0700, "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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can i remove the several hyperlink option after entering the . | Excel Discussion (Misc queries) | |||
How can i remove the several hyperlink option after entering the . | Excel Discussion (Misc queries) | |||
remove characters from a text under a condition | Excel Discussion (Misc queries) | |||
want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions | |||
how do i remove hyphens from between numbers | Excel Worksheet Functions |