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

Don Iliffe

New Member
Joined
Dec 18, 2008
Messages
15
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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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:

Code:
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
 
Last edited:
Upvote 0
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?

Code:
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
How did you use it? You can use it in a cell eg =extract_chars(A1,9)
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top