How to TRIM away unwanted data from a cell

[meppwc]

I have a column that, that if data exists, then I want to trim away everything except for a 7 digit number (if a 7 digit number exists)
The cells can contain, letters, numbers (other than 7 digits next to each other), or be blank

Is there any way to trim away everything except for the 7 digit number (if exists)?

 

[NeonRedSharpie]

Function GETCONSECUTIVE(val As Range, consec As Integer) As Variant

    Dim valTwo As String
    valTwo = val.Value
    Debug.Print Len(valTwo)
    For x = 1 To Len(valTwo)
    
        For y = 1 To consec
            If IsNumeric(Mid(valTwo, x, y)) Then
                GETCONSECUTIVE = GETCONSECUTIVE & Mid(valTwo, x, y)
            Else
                GoTo nope:
            End If
        Next y
        GETCONSECUTIVE = Mid(valTwo, x, consec)
        Exit Function
nope:
GETCONSECUTIVE = ""
    Next x


End Function

Here's a custom function. It will take a cell input, and then the number 7 (or any other length of value you want)

 

[meppwc]

I am lost how to use this custom function................the column with the data is CA...............where do I put this function and how would you use it?

 

[NeonRedSharpie]

You put it into your macro editor, and then you can call it on the worksheet.

 

[meppwc]

ADVERTISEMENT

sorry..........I am really lost............I was able to create it..........and I can see it in User Defined functions.............but I don't know how and where to use it from there?+

Also, do I need to edit this function based on where the data resides (cell, row)

 

[Snakehips]

meppwc,

Here is another UDF that is ok provided that the seven digit number is delimited by spaces?

Function SEVENUM(Strg As Range) As Variant
Arry = Split(Strg)
For c = 0 To UBound(Arry)
If IsNumeric(val(Arry(c))) And Len(Arry(c)) = 7 Then
SEVENUM = Arry(c)
Exit For
End If
Next c
End Function

Use either UDF themas you would a standard function e.g...

Excel 2007
	CA	CB
1	abc 1234567 df 346	1234567
2	abc 1234567 df 345	1234567
Sheet2

Cell Formulas
Range	Formula
CB1	=SEVENUM(CA1)
CB2	=GETCONSECUTIVE(CA2,7)

 

[meppwc]

ADVERTISEMENT

unfortunately it is not delimited..............it may have letters or symbols directly in front of the number

 

[Snakehips]

No probs....
If you are ok to create the 'trimmed' listing in a secondary column as we have assumed then NRS has given you the solution you need.

 

[meppwc]

Hi Tony................I believe the solution from NRS is what I need, and I have been able to create/save the function in the editor...............but now I just don't know how to use it................my data is in column CA, and I am trying to add the function to column CB...........=Module1.GETCONSECUTIVE(), but it returns #VALUE!. If I add CA5 between the training brackets............=Module1.GETCONSECUTIVE(CA5), CB5 is still returning #VALUE!.........so I am lost

 

[meppwc]

nevemind..............DUH..................what an idiot I am..................this worked............Module2.GETCONSECUTIVE(CA5,7)
So sorry for being such an idiot...............thank for all the help