Identifying Gaps

[alexfwalker81]

I have an import template for one of our system which requires all of the address lines to be 'aligned left'. It's a pain, so I wondered if there's a formula which could identify gaps where there is a blank cell. For example, between Smith's Wood and Birmingham below.

 

[theDBguy]

Hi. "Aligned left" where? If you're talking about on display form or report, that's not a problem by simply using an expression to concatenate all the address info into one.

 

[alexfwalker81]

Hi. "Aligned left" where? If you're talking about on display form or report, that's not a problem by simply using an expression to concatenate all the address info into one.

Sorry, terrible explanation.

So, in the image above, there's an empty cell between 'Braunston' and 'Northamptonshire'. To the system that these addresses get imported into, that looks like an empty address line and so it would reject that address. For it to be correct, 'Northamptonshire' would need to be in column G. Ignore the name of the column being 'City' as these are ignored.

A concatenate wouldn't work, as I need everything to stay in distinct cells, just without any empty cells. There can be empty cells to the right of the data though.

Does that make sense?

 

[theDBguy]

Sorry, terrible explanation.

So, in the image above, there's an empty cell between 'Braunston' and 'Northamptonshire'. To the system that these addresses get imported into, that looks like an empty address line and so it would reject that address. For it to be correct, 'Northamptonshire' would need to be in column G. Ignore the name of the column being 'City' as these are ignored.

A concatenate wouldn't work, as I need everything to stay in distinct cells, just without any empty cells. There can be empty cells to the right of the data though.

Does that make sense?

Hi. Thank you for the explanation. I wasn't understanding it earlier because I didn't realize I was reading a question in the Excel forum. I thought it was in Access.

Okay, I do believe there is an expression to find the next "blank" cell to the right. Let me see if I can find a reference for you.

Please stand by...

 

[theDBguy]

Okay, see if any of the information presented here is useful to you.

Find the first empty cell in the same column/row

I am trying to work out a formula that will give me the row number of the first empty cell in a column. Currently I am using: =MATCH(TRUE, INDEX(ISBLANK(A:A), 0, 0), 0) This works fine, unless the

stackoverflow.com

 

[Isaac]

Wait ... Wouldn't a simple concatenation bring you the exact result you're looking for?

Concatenating "one", [blank], "two", "three", returns: onetwothree (no blanks).

Never mind - I see the problem now. How about a custom function? Or how about just a separate set of columns with IF statements to test for where things are? Posting a sample sheet might be ideal in this situation.

 

[Gasman]

Here is something I wrote to do much the same thing.

Sub Tidy_Address()
Dim iLoop1 As Integer, iLoop2 As Integer, iLastRow As Integer
iLastRow = ActiveSheet.UsedRange.Rows.Count
Range("D2").Select
While ActiveCell.Row <= iLastRow
    iLoop = 1
    While iLoop < 4
' MsgBox (ActiveCell.Offset(0, iLoop).Value)
    
        If IsEmpty(ActiveCell.Offset(0, iLoop)) Then
            For iLoop2 = iLoop To 3
            If Not IsEmpty(ActiveCell.Offset(0, iLoop2 + 1).Value) Then
                ActiveCell.Offset(0, iLoop).Value = ActiveCell.Offset(0, iLoop2 + 1).Value
                ActiveCell.Offset(0, iLoop2 + 1).Value = ""
                Exit For
            End If
            Next iLoop2
        End If
    
    iLoop = iLoop + 1
    Wend
ActiveCell.Offset(1, 0).Activate
Wend
End Sub

HTH

 

[alexfwalker81]

Thank you all - the subroutine works brilliantly.