CODEWITHSUNDEEP
excelvba
Maykid
Maykid

Reputation: 517

Extract Text/Numbers before a specific character

So I've been playing around with Excel VBA to see what I can do with it. Currently, I'm stuck on one problem. My code is this: 

Sub Validate_Input_Click()
 Dim temp As String
 For Row = 7 To 250
      If Application.WorksheetFunction.CountBlank(Range(Cells(Row, 2), Cells(Row, 12))) = 0 Then
    temp = ""
    For col = 2 To 12
      If Cells(Row, col) <> "" Then
        If temp <> "" Then temp = temp & "_"
        temp = temp & Cells(Row, col)
      End If
     Next col
    Cells(Row, 1) = temp
 End If
Next Row
End Sub

This works exactly as I want it to. What I'm trying to do now is, lets say in a few cells of columns B through E have Text with a dash then more text, for example:

Test - Testing

What I want to do along with concatenating is, Grab everything to the left of that dash in each individual cell. So it would look something like,

Running_This_Test_In_Some_Kind_Of_Format

instead of:

Running_This_Test - Testing_In_Some_Kind_Of_Format

I've tried creating an integer and creating a Left statement but keeps giving me not enough memory errors or using wrong argument, not sure what I'm doing incorrectly. So any help would be much appreciated!

Upvotes: 0

Views: 771

Answers (5)

QHarr
QHarr

Reputation: 84465

Or the following. It will be fast as uses array, typed functions, used range and compared with vbNullString.

Option Explicit

Public Sub Concat()
    Application.ScreenUpdating = False
    Dim arr(), wb As Workbook, ws As Worksheet, i As Long, j As Long, concatString As String
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Sheet9") 'Change as required

    With ws
        arr = Intersect(.Range("B:E"), .UsedRange)
        For i = LBound(arr, 1) To UBound(arr, 1)
             concatString = vbNullString
            For j = LBound(arr, 2) To UBound(arr, 2)
                If InStr(1, arr(i, j), "-") > 0 Then concatString = concatString & Left$(arr(i, j), InStr(1, arr(i, j), "-") - 1)
            Next j
            .Cells(i, 1) = Join$(Split(Trim$(concatString), Chr$(32)), "_")
        Next i
    End With
    Application.ScreenUpdating = True
End Sub

Data:

Data

Upvotes: 0

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

There is no need to check for empty cell again as you are already checking them with CountBlank.

What about this?

Sub Validate_Input_Click()
 Dim temp As String, str As String
 Dim iRow As Long, Col As Long
 For iRow = 7 To 250
    If Application.WorksheetFunction.CountBlank(Range(Cells(iRow, 2), Cells(iRow, 12))) = 0 Then
    temp = ""
    For Col = 2 To 12
        str = Trim(Split(Cells(iRow, Col), "-")(0))
        If temp = "" Then
            temp = str
        Else
            temp = temp & "_" & str
        End If
     Next Col
    Cells(iRow, 1) = temp
 End If
Next iRow
End Sub

Upvotes: 0

Maykid
Maykid

Reputation: 517

In messing around with the code I think I found another solution to my own problem. The code looks like:

Sub Validate_Input_Click()
Dim temp As String
Dim s As String
For Row = 7 To 250
 If Application.WorksheetFunction.CountBlank(Range(Cells(Row, 2), Cells(Row, 12))) = 0 Then
    temp = ""
    For col = 2 To 12
      If Cells(Row, col) <> "" Then
      s = temp
        If temp <> "" Then temp = Split(s, " - ")(0) & "_"
        temp = temp & Cells(Row, col)
      End If
    Next col
    Cells(Row, 1) = temp
End If
Next Row
End Sub

Would this be a viable solution as well? Or would something else work better like the answer above from @dwirony?

Upvotes: 0

Tehscript
Tehscript

Reputation: 2556

You can replace

temp = temp & Cells(Row, col)

with

pos = InStr(1, Cells(Row, col), "-", vbTextCompare) 'find the position of dash
If pos Then 'if dash position exists
    temp = temp & Trim(Left(Cells(Row, col), pos - 1)) 'take left part of that string and trim to get rid of spaces
Else
    temp = temp & Cells(Row, col) 'else do it as you did it before
End If

Upvotes: 1

dwirony
dwirony

Reputation: 5450

Some slight alterations made... probably not the cleanest solution, but a solution nonetheless:

Sub Validate_Input_Click()
Dim temp As String, nextstring As String
Dim i As Long

For Row = 7 To 250
    If Application.WorksheetFunction.CountBlank(Range(Cells(Row, 2), Cells(Row, 12))) = 0 Then

    temp = ""

    For col = 2 To 12
      If Cells(Row, col) <> "" Then

        If InStr(Cells(Row, col), "-") > 0 Then
            For i = 1 To Len(Cells(Row, col))
                If Mid(Cells(Row, col), i, 1) = "-" Then
                    nextstring = Left(Cells(Row, col), i - 2)
                    Exit For
                End If
            Next i
        Else
            nextstring = Cells(Row, col)
        End If

        If temp <> "" Then temp = temp & "_"
            temp = temp & nextstring
        End If
    Next col

    Cells(Row, 1) = temp

    End If
Next Row

End Sub

Image1

Upvotes: 0

Related Questions