How to Filter a column by text which has blank space in start

[Sachin2k6]

Hi friends,
i have a data column in excel which has text containing blank space in start in some cells. how can i filter these cells which has text containing blank space in start.

 

[learnerrr]

You can use the Trim function.

 

[BSALV]

i'm not sure of 2007 already had textfilters in its filterfunction otherwise you can use those with "contains"

 

[Siddharth Rout]

Add a helper column and use the below formula. I have used D and E as an example. Edit the formula accordingly.

=LEFT(D2,1)=" "

And then filter on TRUE in the helper column

 

[Sachin2k6]

Add a helper column and use the below formula. I have used D and E as an example. Edit the formula accordingly.

=LEFT(D2,1)=" "

And then filter on TRUE in the helper column

View attachment 56019

Thanks for helping but can u tell any other way which not required adding helper column?

 

[Siddharth Rout]

Thanks for helping but can u tell any other way which not required adding helper column?

Would a VBA solution work for you?

If yes, then try this

I am assuming that the data is in Col "A" of "Sheet1". Change the code below accordingly.

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long, i As Long
    Dim FilteredRange As Range
   
    '~~> Change this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
   
    With ws
        .AutoFilterMode = False
       
        '~~> Find last row in Col A
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
       
        '~~> Identify the rows that needs to be hidden
        For i = 2 To lRow
            If Left(.Range("A" & i).Value2, 1) <> " " Then
                If FilteredRange Is Nothing Then
                    Set FilteredRange = .Rows(i)
                Else
                    Set FilteredRange = Union(.Rows(i), FilteredRange)
                End If
            End If
        Next i
       
        '~~> Hide rows if applicable
        If Not FilteredRange Is Nothing Then FilteredRange.EntireRow.Hidden = True
    End With
End Sub

 

[Fluff]

Are you still using xl2007?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[learnerrr]

Hello everyone,

I don't know what my first answer was about, maybe because I was just waking up. Anyway, here's another solution similar to Rout's.

Sub AnotherSample()
    Dim cell As Range, DataRange As Range, TargetRange As Range
    
    'change this to refer to your actual worksheet
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    
    'change this to refer to range that includes your data
    Set DataRange = ws.UsedRange.Offset(RowOffset:=1)
    
    On Error Resume Next
    With ws
        .ShowAllData
        .Cells.EntireRow.Hidden = False
    End With
    On Error GoTo 0
    
    For Each cell In DataRange
        If cell.Value2 Like "[ ]*" Then
            If TargetRange Is Nothing Then
                Set TargetRange = cell.EntireRow
            Else
                Set TargetRange = Union(TargetRange, cell.EntireRow)
            End If
        End If
    Next
    
    If Not TargetRange Is Nothing Then TargetRange.EntireRow.Hidden = True
End Sub

 

[learnerrr]

Hello everyone,

I don't know what my first answer was about, maybe because I was just waking up. Anyway, here's another solution similar to Rout's.

Sub AnotherSample()
    Dim cell As Range, DataRange As Range, TargetRange As Range
   
    'change this to refer to your actual worksheet
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
   
    'change this to refer to range that includes your data
    Set DataRange = ws.UsedRange.Offset(RowOffset:=1)
   
    On Error Resume Next
    With ws
        .ShowAllData
        .Cells.EntireRow.Hidden = False
    End With
    On Error GoTo 0
   
    For Each cell In DataRange
        If cell.Value2 Like "[ ]*" Then
            If TargetRange Is Nothing Then
                Set TargetRange = cell.EntireRow
            Else
                Set TargetRange = Union(TargetRange, cell.EntireRow)
            End If
        End If
    Next
   
    If Not TargetRange Is Nothing Then TargetRange.EntireRow.Hidden = True
End Sub

View attachment 56034View attachment 56035

I messed up again ?, here's the correct solution:

Sub AnotherSample()
    Dim cell As Range, DataRange As Range, TargetRange As Range
    
    'change this to refer to your actual worksheet
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    
    'change this to refer to range that includes your data
    Set DataRange = ws.UsedRange.Offset(RowOffset:=1)
    
    On Error Resume Next
    With ws
        .ShowAllData
        .Cells.EntireRow.Hidden = False
    End With
    On Error GoTo 0
    
    For Each cell In DataRange
        If cell.Value2 Like "[ ]*" Then
            If TargetRange Is Nothing Then
                Set TargetRange = cell.EntireRow
            Else
                Set TargetRange = Union(TargetRange, cell.EntireRow)
            End If
        End If
    Next
    
    DataRange.EntireRow.Hidden = True
    If Not TargetRange Is Nothing Then TargetRange.EntireRow.Hidden = False
End Sub

 

[Peter_SSs]

i'm not sure of 2007 already had textfilters in its filterfunction otherwise you can use those with "contains"

What would you enter into the "contains" box?

Here is another possible macro approach assuming that the data is not the result of formulas that need to be retained.

Sub Filter_Blank_First()
  ActiveSheet.FilterMode = False
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(left(#,1)="" "",""%%%"","""")&#", "#", .Address))
    .AutoFilter Field:=1, Criteria1:="%%% *"
    .Replace What:="%%%", Replacement:="", LookAt:=xlPart
  End With
End Sub

Sample data before ..

Sachin2k6.xlsm
	A
1	Data
2	Data 1
3	Data 2
4	Data 3
5	Data 4
6	Data 5
7	Data 6
8	Data 7
9
10	Data 9
11	Data 10
12	Data 11
13	Data 12
14
Sheet1

.. and after:

Sachin2k6.xlsm
	A
1	Data
4	Data 3
6	Data 5
7	Data 6
11	Data 10
14
Sheet1