Identifying Gaps (1 Viewer)

alexfwalker81

Member
Local time
Today, 06:41
Joined
Feb 26, 2016
Messages
93
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.

gaps.png
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:41
Joined
Oct 29, 2018
Messages
21,358
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

Member
Local time
Today, 06:41
Joined
Feb 26, 2016
Messages
93
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

I’m here to help
Staff member
Local time
Today, 06:41
Joined
Oct 29, 2018
Messages
21,358
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

I’m here to help
Staff member
Local time
Today, 06:41
Joined
Oct 29, 2018
Messages
21,358
Okay, see if any of the information presented here is useful to you.

 

Isaac

Lifelong Learner
Local time
Today, 06:41
Joined
Mar 14, 2017
Messages
8,738
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

Enthusiastic Amateur
Local time
Today, 13:41
Joined
Sep 21, 2011
Messages
14,052
Here is something I wrote to do much the same thing.
Code:
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
 

Users who are viewing this thread

Top Bottom