Extracting data within rows containing different lengths of Excel data

[wec43wec]

Is there a way to use the "left, Mid, etc" function in Excel to extract data within a row,from a database that has different lengths of data within the same cell/row?

I want each item to fall into a seaprate cell within Excel.

Problem example:

123456789 50th Street City State Rt # X1234
123456789 Apple Avenue Apt #1 City, State RT# X123456


Solution Layout:

123456789(space)50th Street(space)City (space)State(space)Rt#X12

123456789(space)Apple Avenue(Apt # 1(space)City(space)State(space) Rt#1234

 

[TomCologne]

remove street address from address
thread68-1387851

TomCologne

 

[AnotherHiggins]

Tom,

The best solution in the thread to which you link is to get the data file from the customer again, this time in a reasonable format.

wec43wec,

As was pointed out in the other thread, your problem will be the exceptions. Pretty much any record that doesn't fit the norm will be wrong.

It is easy enough to isolate everything before the first space.
It is easy enough to isolate everything after the first space.
It is easy enough to isolate everything between, say, the second space and the third space.

But you are dealing with an unknown number of spaces. And there's no telling if extra spaces are in the city name, separate an apartment number, etcetera.

As I said in the other thread, this is why data shouldn't be stored like this!

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[TomCologne]

John,

...data shouldn't be stored like this!

You're absolutely right!

The reason I mentioned the thread was not its solution, ie a reasonable format provided by the customer, but the discussion about the problems.


TomCologne