Multiple values in one cell, need to be separated

[YellowPerch]

All,

New to the forum and looking for some assistance with, what I believe, is a uniquely difficult issue.

I have a table displaying information pertaining to land grid information. The table has three main fields of interest: Sections, Townships, and Ranges. Sections are expressed as a on or two-digit number (1-36) and Townships/Ranges are expressed with a one or two digit number with a letter attached (i.e. 1N or 47W). My goal is to combine these three pieces of information into a unique key (i.e. 231N47W) to use for further analysis.

The big issue is that the table containing the raw information was formatted in such a way that if a record references a location pertaining to more than one land grid section/township/range, the creator used carriage returns to include multiple values for the cell. For example:

If record 'ShapeOne' contains a location in Section 3, Township 11N, Range 55W AND Section 4, Township 11N, Range 55W: the contents of the 'Section' cell will be presented with two characters ('3' & '4') separated by a carriage return. This strategy is also implemented in the 'Township' and 'Range' columns.

Ideally the creator of the table would have created a separate row for each unique location.

I have used a formula to isolate and eradicate the carriage return delineator using this formula:

=SUBSTITUTE(H2,CHAR(10),"; ")

In a new column titled 'SectionFix', the multiple values are presented as a string separated by semicolons. I have done the same for Twonship and Range columns.

My question is, what is the best strategy for splitting the semicolon-delineated values and creating unique rows for each record while preserving (duplicating) the corresponding information?

Any help would be greatly appreciated, thanks in advance!

[tigeravatar]

YellowPerch,

Welcome to the forum!
You could copy/paste special -> values the column with the semicolon, and then use text-to-columns with the semicolon as the delimiter.