VBA solution to finding a string in a cell that is N chrs in length

[Don Iliffe]

I am trying to find a VBA solution to finding a string in a range of cells that is of a specified chrs length.

The purpose is to find, and then copy this string, and all other instances, to another worksheet. The copying part is not a problem, but I cannot come up with a solution that finds a string in the cells that is n chrs in length. (The actual length is nine(9) chrs)

Does anyone have any ideas on this.

 

[hiker95]

Don Iliffe,

Welcome to the MrExcel forum.

It would help if we could see your actual raw data workbook/worksheets, and, what the results (manually formatted by you) should look like.

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

 

[Don Iliffe]

ARRRGGHH
I thought this had gone away, now it has come back to haunt me


Problem: I have several large HUGE) spreadsheets that contain extracted config information. One column contains the information I need to extract in the form of a nine char substring. Unfortunately it is mixed in with a large amount of other data of varying lengths and formats.


Example:
-**UNUSED-PORT** 06OLOLPR0B FRP
Tu27462908 04ITOLR04 A4r303
Gi0/x 57GEGER03 ipZ 77fgh

and so on and so on,
The info I want to extract to another column is the 9 chr substring (shown in BOLD above) usually buried in all of this.


Need: I am looking for way to be able to extract them either via a formula, VBA macro or UDF to a seperate column.

 

[NewOrderFac33]

Assuming the string you're searching for will always be 9 digits and that it will always occur after the first occurence of a space, try =MID(D4,(FIND(" ",D4)+1),9) where your value is in D4.

Pete

 

[Don Iliffe]

Thanks for the reply
Unfortunately that is an assumption that cannot be made. The Only certainty is that the group is 9 chars in length and generally preceded by a space

 

[steve the fish]

Your first one in bold is ten characters?? This UDF will find the first 9 character string in a cell that is separated by spaces:

Function extract_chars(the_range As Range, number_of_characters As Long) As Variant

Dim arr, i As Long

If the_range.Cells.Count > 1 Then
    extract_chars = CVErr(xlErrNA)
Else
    arr = Split(the_range.Value, " ")
    For i = LBound(arr) To UBound(arr)
        If Len(arr(i)) = number_of_characters Then
            extract_chars = arr(i)
            Exit Function
        End If
    Next
    If Len(extract_chars) = 0 Then extract_chars = CVErr(xlErrNA)
End If

End Function

 

[tyija1995]

I've made this macro that will highlight any cells in a range that match the specified number of characters (via user input)

Not sure if its exactly what you're looking for but maybe something to build on?

Sub StringChecker()
    Dim myLen As Integer
    Dim strLen As Integer
    Dim rng As Range
    Dim cell As Range
    Dim txt As String
    
    myLen = InputBox("Please enter a length of string to be checked for in a selected range.")
    
    Set rng = Selection
        For Each cell In rng
            txt = cell.Value
            strLen = Len(cell.Value)
                If strLen = myLen Then
                    cell.Interior.ColorIndex = 36
                End If
        Next cell
End Sub

 

[Don Iliffe]

Err Whoops,

In my own defence I am going through 167,000+ lines.

I will try these in a sample workbook and get back to you. As you say pointing me in the right direction. Many thanks.

 

[Don Iliffe]

Steve the Fish

have tried your UDF. it appears to append a dash to the left of the existing cell data. Does not appear to extract 9 char sub-string at all. Is there something I'm missing here?

 

[steve the fish]

How did you use it? You can use it in a cell eg =extract_chars(A1,9)