Text to Column - sorting data into appropriate fields

  • Thread starter Thread starter sky
  • Start date Start date
S

sky

:confused: Hi Everyone,
I have address separated by comas. I need to separate them into
columns. The max number of columns are nine(9), however not all are
filled. The first four (4) are not mandatory fields. See below (the
first address I have 7 comas and the second has 6 comas)

Example One:
123 Room, Xie Wan Village Guangzhou Dadao Bei Road, Tonghe Town,
Guangdong District, Guangzhou City, Leiming Province, PR China, 510505
Example Two
9 4th Building, 116 Street, Qing Shan District, Hainan City, Hunan
Province, PR China, 430021

The Problem:
When I use the Text to Column wizard; it sorts from Left to Right
depending the number of comas I have in the string. If I had an equal
number of comas then all the columns would line up. However as address
length vary the data does not line up in the correct columns.

Is there any way to sort from Right to Left?? Any other suggestions
would be appreciated.

Many Thanks in advance.

Regards
Robin
 
Maybe you could just insert some commas in front of the string.

If your data is in column A, you could use this formula in B and drag down:

=REPT(",",9-(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))&A1

Then select column B
Edit|copy
edit|paste special|Values

Then use data|text to columns against column B (delimited by a comma).
 
sky said:
:confused: Hi Everyone,
I have address separated by comas. I need to separate them into
columns. The max number of columns are nine(9), however not all are
filled. The first four (4) are not mandatory fields. See below (the
first address I have 7 comas and the second has 6 comas)

Example One:
123 Room, Xie Wan Village Guangzhou Dadao Bei Road, Tonghe Town,
Guangdong District, Guangzhou City, Leiming Province, PR China, 510505
Example Two
9 4th Building, 116 Street, Qing Shan District, Hainan City, Hunan
Province, PR China, 430021

The Problem:
When I use the Text to Column wizard; it sorts from Left to Right
depending the number of comas I have in the string. If I had an equal
number of comas then all the columns would line up. However as address
length vary the data does not line up in the correct columns.

Is there any way to sort from Right to Left?? Any other suggestions
would be appreciated.

Many Thanks in advance.

Regards
Robin
Click to expand...

Reversing the direction can be done via a custom macro using the midstr$
function -- however the results will remain the same until your input data
has the same number of commas on each row. Whether cleaning the results is
easier before/after you do the text-to-columns conversion depends on how you
are getting the input data.
 
Back
Top