find and replace help needed formula or macro
Posted by Ed on January 06, 2002 9:50 AM
Need help with a formula or a macro
Have a list of part numbers, many different product lines,
Many different types of numbers
Need to remove all dashes from only the part numbers
That contains two dashes side by side
A number may look like this 45-AX478-P-5
The two dashes always follow the two letters
But there may be 1,2,or 3 numbers before the letters
Find and replace with nothing works great but,
Only removes the two dashes.
Thanks in advance for any help Ed
Posted by Aladin Akyurek on January 06, 2002 10:11 AM
Ed --
If you want to remove all dashes from the part numbers, insert a column next to the column where you have part numbers.
Then enter what follows in the cell that corresponds to the first part number:
=SUBSTITUTE(A2,"-","")
where A2 is the cell that contains the first part number that you want sripp off dashes.
Copy down this as far as needed. Then select all the cells of the column of this formula, copy, then Edit|Paste Special > Values over the original column and delete the new column.
Aladin
=======
Posted by Solinus on January 06, 2002 9:04 PM
Or perhaps ......
ERR
Posted by Aladin Akyurek on January 07, 2002 1:59 AM
Re: Or perhaps ......
Solinus --
SUBSTITUTE returns the string as is when there is no substring (e.g., "-") in the string (e.g., "Solinus"), so there is no need for IF(ISERROR(FIND... as you formulate it.
Regards,
Aladin
=======
Posted by Solinus on January 07, 2002 7:54 AM
Sorry, don't understand ....
... my understanding was that all dashes have to be removed only if the part number contains two consecutive dashes.
I don't think your formula meets this requirement but mine does - but am prepared to be proven wrong.
Posted by Aladin Akyurek on January 07, 2002 8:25 AM
Re: Sorry, don't understand ....
Solinus --
> ... my understanding was that all dashes have to be removed only if the part number contains two consecutive dashes.
> I don't think your formula meets this requirement but mine does - but am prepared to be proven wrong.
Not sure what Ed exactly wants: I interpreted his question as "remove all dashes".
My reply to you involves a different issue: Whether the ISERROR test is needed when using SUBSTITUTE as you did:
Lets take the following sample
{"a1--kad-x";"a1kad"}
say in A1:A2.
In B1 enter: =IF(ISERROR(FIND("--",A1)),A1,SUBSTITUTE(A1,"-","")) [ your fomula ]
In C1 enter: =SUBSTITUTE(A1,"--","")
Select B1:C1 and copy down. What we see including the sample:
{"a1--kad-x","a1kadx","a1kad-x";
"a1kad","a1kad","a1kad"}
Hope this helps.
Aladin
=============
Posted by Solinus on January 07, 2002 8:51 AM
Sorry, doesn't help ......
..... I must be missing something here.
The example and results you posted show that my formula produces the required result ("a1kadx") whereas yours doesn't ("a1kad-x").
(Also, the ISERROR test is for FIND, not SUBSTITUTE.)
PS
Ed said that he needed :-
"to remove all dashes from only the part numbers
That contains two dashes side by side"
Solinus -- > ... my understanding was that all dashes have to be removed only if the part number contains two consecutive dashes.
Posted by Aladin Akyurek on January 07, 2002 9:55 AM
Re: Sorry, doesn't help ......
Solinus --
I'm apparently not able to explain what I took to be the issue.
By the way,
In D1 enter: =SUBSTITUTE(A1,"-","") [ which is the formula I suggested to Ed, according to my understanding what he wanted, that's Remove all hyphens ]
This shorter formula does exactly what you formual does.
Cheers.
Aladin
====== ..... I must be missing something here.
Posted by Solinus on January 07, 2002 3:51 PM
Not quite ......
I'm apparently not able to explain what I took to be the issue.
I THOUGHT YOUR ISSUE WAS ABOUT USNG "ISERROR".
YOU SAID MY FORMULA DOES NOT NEED THIS BUT I SAY IT DOES. By the way, In D1 enter: =SUBSTITUTE(A1,"-","") [ which is the formula I suggested to Ed, according to my understanding what he wanted, that's Remove all hyphens ] This shorter formula does exactly what you formual does.
NO IT DOESN"T.
YOUR FORMULA REMOVES ALL HYPHENS FOR ALL PART NUMBERS (WHICH IS WHAT YOU UNDERSTOOD TO BE REQUIRED).
MY FORMULA REMOVES ALL HYPHENS ONLY FOR PART NUMBERS COTAINING SIDE BY SIDE HYPHENS (WHICH IS WHAT I UNDERSTAND THE REQUIREMENT TO BE). Cheers. ====== : ..... I must be missing something here.
