Remove dashes when between letter and number REVISITED 2

[rcrecelius]

thread68-907911

Back in 2004 I asked this question and KenWright gave me a formula that has saved me countless hours of frustration...so let me starrt by saying thanks again Ken!

Now today, I am realizing that this formula does have a shortfall. Here is my original question from 2004

I have part numbers which may have a combination of letters, dashes, and numbers. I have to go thru and manually delete the dashes from some part numbers but not all of them.
This is the desired "format"...
If the dash is between a letter and a number - delete it.
If the dash is between 2 numbers or 2 letters - keep it.

"Good examples" - keep the dash
A10-1
204-166
735-10
2035E-S

"Bad examples" - delete the dash
A-10
2035-ES

Ken provided me this formula which works as advertised on the above examples and I have used it a LOT.
=IF((ISNUMBER(--MID(A1,FIND("-",A1)-1,1))*ISNUMBER(--MID(A1,FIND("-",A1)+1,1))+NOT(ISNUMBER(--MID(A1,FIND("-",A1)-1,1)))*NOT(ISNUMBER(--MID(A1,FIND("-",A1)+1,1)))),A1,SUBSTITUTE(A1,"-","",1))

Now...I am finding that I have multiple instances of the dash that need to be considered for removal...the above formula is only looking at the first dash.

EX: CB2.5L-C-1 Desired output CB2.5L-C1
The dash between the C and 1 needs to be removed but I think the formula is only addressing the first dash it finds, which in this case does not need to be deleted.

Is there anything that can be done to enhance this formula so that it will look at multiple dashes within the same cell?

 

[Andrzejek]

If that's in Column A:[tt]
A-10
2035-ES
[/tt]
and you would like to have in Column A:[tt]
A10
2035ES
[/tt]
Would you be open for a simple VBA code (macro) to run to do it for you? Instead of formula.

Have fun.

---- Andy

 

[rcrecelius]

Andy,
Definitely yes that sounds appealing. You might have to walk me through it...I use macro's but havent looked at the actual VBA code in a long time.

 

[Andrzejek]

Ona additional question before I start coding: When you have any dashes, could you have more than 1 dash in a string? Or there could be only one, or none, but no more than 1.

Have fun.

---- Andy

 

[rcrecelius]

Andy,
See the last part Of the original post for an example...yes there can be multiple dashes.

 

[xlbo]

I would do this in a user defined function tbh - brute force but easy to understand:

Public Function Strip_Dashes(strInput As String)
Dim pos As Integer
Dim preType As Integer
Dim postType As Integer
Dim strOutput As String

strOutput = ""

For pos = 1 To Len(strInput)

    If Mid(strInput, pos, 1) = "-" Then
    'check before and after characters
        preType = Asc(Mid(strInput, pos - 1, 1))
        postType = Asc(Mid(strInput, pos + 1, 1))
       
        If ( _
            preType >= 48 And preType <= 57 _
            And postType >= 48 And postType <= 57 _
            ) _
            Or _
            ( _
            preType >= 65 And preType <= 122 _
            And postType >= 65 And postType <= 122 _
            ) Then
       
        'either both numbers or both letters. Keep the dash
        
            strOutput = strOutput & Mid(strInput, pos, 1)
        
        Else
       
        'mix of numbers / letters - remove the dash
            
            
        End If
        
    Else
    
        strOutput = strOutput & Mid(strInput, pos, 1)
        
    End If

Next pos

Strip_Dashes = strOutput

End Function

This should be placed in a standard module and is called using =strip_dashes(A1)
where A1 contains the text to be checked

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[jges]

Here's an alternative that uses regular expressions:

Public Function Strip_Dashes(strInput As String)
Dim re As Object
Set re = CreateObject("vbscript.regexp")

[COLOR=green]'check for a letter followed by a dash followed by a number
'parentheses create 'groups'; group 1 is the letter, group 2 is the number[/color]
re.Pattern = "([a-z])\-(\d)"
re.IgnoreCase = True
re.Global = True

[COLOR=green]'if pattern found, remove dash (replace Letter-Number with LetterNumber)[/color]
strInput = re.Replace(strInput, "$1" & "$2")

[COLOR=green]'change pattern to look for number followed by dash followed by letter
'parentheses create 'groups'; group 1 is the number, group 2 is the letter[/color]
re.Pattern = "(\d)\-([a-z])"

[COLOR=green]'if pattern found, remove dash (replace Number-Letter with NumberLetter)[/color]
strInput = re.Replace(strInput, "$1" & "$2")

Strip_Dashes = strInput

Set re = Nothing

End Function

 

[rcrecelius]

A star to both of you for helping me with this...I saved both versions and so far they both work well.
It did take me a bit to figure out how to actually run the function as I was still thinking in terms of a macro with an assigned keyboard shortcut. Once I zoned in on the fact that this was a "function"(duh), I went to User Defined and found Strip_Dashes right there and ready to use.
I have found though that I cant just type it into the formula bar, is that normal for a user defined function?

 

[jges]

This should be placed in a standard module and is called using =strip_dashes(A1)
where A1 contains the text to be checked

Using xlbo's instructions, I can type it into the formula bar. What version of Excel are you using? I'm stuck on 2000 for now (not for too much longer, I hope). Perhaps something is different with newer versions?

 

[rcrecelius]

I'm on 2010.
I saved this to the hidden Personal workbook like I do all my other macros so I can use it with any workbook...I see now, I would have to type in "=PERSONAL.XLSB!Strip_Dashes(A1)"
What should I have done differently to just type in "=strip_dashes(A1)"?
Its not a big deal really because it is going to save me a LOT of time already.

 

[jges]

I just started a blank workbook, opened the VBA editor, inserted a module, and added the function there. I didn't try adding it to the personal workbook. All my coding and testing was done in the new, blank workbook.

 

[xlbo]

To enable you to just use =strip_dashes(A1), all you need to do is have the function in a standard module in the workbook that you want to call the function from

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question