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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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:

Don Iliffe

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

Well-known Member
Joined
Sep 26, 2011
Messages
1,261
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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:

Don Iliffe

New Member
Joined
Dec 18, 2008
Messages
15

ADVERTISEMENT

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

Well-known Member
Joined
Oct 20, 2009
Messages
8,644
Office Version
  1. 365
Platform
  1. Windows
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:

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
781
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

Don Iliffe

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

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

Well-known Member
Joined
Oct 20, 2009
Messages
8,644
Office Version
  1. 365
Platform
  1. Windows
How did you use it? You can use it in a cell eg =extract_chars(A1,9)
 

Forum statistics

Threads
1,148,293
Messages
5,745,912
Members
423,983
Latest member
blackworx

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
Top