Trim Text & Special Characters - Leave numbers and "-" only

[AbrahamGluck]

Hello people,

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>sample sample sample sample sample (sample: 111-1234567-1234567)	111-1234567-1234567

<tbody>
</tbody>

I'm looking to create a formula to remove all text and special characters (in this case it can only be open pregnancies and closed pregnancies) and leave the numeric value and the - character.
see my example.

 

[Dim Me as xlNoob]

Are you happy with using a user defined function? I have no idea how to do this with existing functions in excel but am happy to offer a solution via UDF.

 

[Dim Me as xlNoob]

Before I get into it though I better ask will the format and length of the number always be the same and always at the end of the brackets like that? Because that would just simply be:

=LEFT(RIGHT(A1,20),19)

But I don't know why I just instantly assumed you wanted something way more dynamic to cater for different length numbers and if other things were in the brackets after the number or something.

 

[JoeMo]

You haven't shown us what variations of the sample string you are working with. If the format/length of the piece to be extracted are fixed, then post #3 offers a formula solution. If not, assuming your strings are text and numbers and/or text and ##-####-###... with space delimiters and the only special characters being "-" or parentheses, here's a UDF you can try.

To install the UDF:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the UDF from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Use the UDF as you would any native Excel function (see example below).
7. Make sure you have enabled macros whenever you open the file or the code will not run.

Excel Workbook
	A	B
1	sample sample sample sample sample (sample: 111-1234567-1234567)	111-1234567-1234567
Fixtures

Function ExtractNumsAnd(S As String)
Dim V As Variant, i As Long, T As Variant
S = Replace(Replace(S, "(", ""), ")", "")
V = Split(S, " ")
For i = LBound(V) To UBound(V)
    If Val(V(i)) <> 0 Then
        T = T & " " & V(i)
    End If
Next i
If T <> "" Then
    ExtractNumsAnd = Mid(T, 2, Len(T))
Else
    ExtractNumsAnd = ""
End If
End Function

 

[AbrahamGluck]

Are you happy with using a user defined function? I have no idea how to do this with existing functions in excel but am happy to offer a solution via UDF.

Thanks very much I'll go ahead with the simple formula first, until i'll see the number gets more dynamic but as far I need it now this is enough. Thanks for all Dim & Jeo

 

[Dim Me as xlNoob]

After learning a bit about regular expressions I thought back to this thread. In case you end up requiring something a little more dynamic I thought you could use a UDF to find the last string of numbers delimited with "-" in case the text beforehand contains something similar. This will find numbers of any length with as many "-" delimiters in it.

1-1
11-12345-12345
124124124-124-12412-412-41-5467-25-63-6-34-6-2

Whatever happens to be there. It will match all substrings like that in the target string and return the last match assuming the one you want will always be at the end of the string.

Function RTNNUMS(str As String) As String
    With CreateObject("VBScript.regexp")
        .Global = True
        .Pattern = "([0-9]*)(-[0-9]*)(-[0-9]*)*"
        If .test(str) Then RTNNUMS = .Execute(str)(.Execute(str).Count - 1)
    End With
End Function

I know you said you were happy with the formula but just thought I would share.