Cleaning up address Data

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

Datasets

Accepted answers

MilindG

@cablejarrett See if this works

Address.yxmd

All comments

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.

Staging address examples.xlsx

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

Staging address examples.xlsx

MilindG

@cablejarrett See if this works

Address.yxmd

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703