Cleaning up address Data

[cablejarrett]

I currently have a large file where the street address is anywhere in 3 different rows address line 1, address line 2, or address line 3. I would like to be able to pick out where the cell that starts witha  number is populated in a new row to help clean this up.  Is that possible?

Jarrett

[binu_acs]

@cablejarrett Would you be able to provide some sample data and expected output?

[cablejarrett]

yes. 

I cant put the actual data but I put what I expected in D 

basically any of those that start with a number I want to put into a new row. 

[MilindG]

@cablejarrett 

[cablejarrett]

thank you for this. 

it almost workedd 

I have a file with more data on it than address that I want to keep (sorry I cant share the entire file) 

another issue is it shifted up and screwed up those that didnt have a number at all. (those I just would need to be blank. 

attached a new sample

[MilindG · Accepted solution]

@cablejarrett See if this works

[ArnaldoSandoval]

Hi @cablejarrett 

You can clean up these addresses fields with the REGEX_Match() function, it returns true/false when the pattern is found/not found, just like this:

if REGEX_Match([Address Line 1 ], "\d.*") Then [Address Line 1 ]
ElseIf REGEX_Match([Address Line 2], "\d.*") Then [Address Line 2]
ElseIf REGEX_Match([Address Line 3], "\d.*") Then [Address Line 3]
Else 'BLANK'
Endif

The condition returns the Address fields fullfilling the condition starting with number; if 2 or more fields start with number, the first field is taken.

Results:

hth

Arnaldo