Getting weird values consistently formatted to proper number of characters -text & numbers

I have a bear of a spreadsheet I need to format and massage on a monthly basis and I feel like you experts would have a better idea of how best to go about this.

I have a range of values that all need to ultimately end up looking like this: 060480 or 052447b <- 6 numeric values possibly followed a letter. Once I have them formatted, I need to sort them in order so that I can merge them into another spreadsheet.

Here're some examples of my original output:

1161
1174
1256_001
1257_001
1258_001
1259_001
12592_001
12601_001
12601a_001
12601b_001
12601c_001
12601d_001
12601e_001
12603_001
12603a_001
12603b_001
12649_001
1265_001
12683a_001
12683a_002
12683b_001
12683b_002
12683c_001
12683c_002
12683d_001
12683d_002
12683e_001
12683e_002
12703w_001
12703ww_001
12703x_001
12703xx_001
12703y_001
12703yy_001
12703z_001
12703zz_001
139
1390
13900_001
1472
1472_1

As you can see, it's really inconsistent. Values with _002 are secondary and the rows will be deleted entirely. Starting out, I could strip out everything after the underscores, but then I lose the information that lets me know that it's the secondary value (which I can get rid of later, but there's another column dependent upon my knowing that information).

The way I've been doing it has been to do a lot of manual sorting, but I have about 11,000 rows to do

So to sum up, the first part of the value, the numbers, needs to be 6 numbers, with leading zeroes. Then the alphabetic information needs to be spared.

I think I can start by deleting all of the _001s and _1s strings, then somehow deleting all rows that still retain an underscore (is there a formula or script for that?)

Once that is done, how do I get the numeric part of the value to 6 numbers?

I attached the spreadsheet I'm working on. Column B is dependent upon Column A, so all of my work is on Column B, and sorting, etc. is done on both columns together.

Any advice is greatly appreciated!

metadataformatting.xlsx

Yep - I downloaded it again to check. If you scroll down to row 1421, they start to show up.

Thanks FDibbins. Unfortunately, it bodges out the alphanumerics in some of the values.

Here's what I've come up with so far (later to be made into one giant, nasty macro...):

• Strip .TIF from Filenames
• Strip _001 and _1 from Filenames
• Apply formula =IF(FIND("_",B2),"TRUE","FALSE") to Column C
• Copy and paste values for Column C
• Delete all rows with TRUE in it
• Move S (slides) to Sheet 2 (deal with those later)
• Move tripartate values to Sheet 3 (deal with those later)
• Strip out alphabetics and -* and paste to Column C

^^^^ That last bit is where I'm currently stuck, because it can be either one or two values from the right, depending on if it's "a", "AA", "b", etc. Any ideas on this part of it?

If I can get the alphabetics out, sort on column B, then move column C back into column B, then that should be it (for the first stage of this project, anyway).

Coincidentally, I got an email this morning from my client telling me how they're helpfully going to streamline their process and are coming up with a new numbering system. To which all I can think is that I now have a new number format to deal with. Ahhhhhhhhh!!!!!!!!!!!!

Great. That works well to get rid of the trailing stuff. Now I'm trying to get it like this, because sorting won't work correctly on numerics vs. alphanumeric strings:

From this:

A	B	C
27508025	003564b
27508119	003565b
28321497	003971b
28321532	003971c
30298558	01338A
30298592	01338AA
30298640	01338B
30298674	01338BB

To this:

A	B	C
27508025	003564	b
27508119	003565	b
28321497	003971	b
28321532	003971	c
30298558	01338	A
30298592	01338	AA
30298640	01338	B
30298674	01338	BB

And, of course, the number of numbers and the number of alphabetical characters is different, so I don't think I can do a simple RIGHT command. If I end up with something like this, then I can sort the whole batch on Column B, then concatenate C back onto the end of B values after sorting.

Ok, gave it a shot and I didn't get very far. Column C worked just fine, but then it looked like it kind of fell apart after that...?

sdmomexcelformulas.gif

Hmm. Still getting the same #N/A result.

sdmomexcelformulas2.gif

ah, no, I hadn't. My bad!

So I'm not entirely sure what I'm looking at - what is column D? I think I'm still stuck in a place where I can't sort on the numeric part of the values (and ignoring the letters)

Oh, that's brilliant! So very, very close. There are a bunch of duplicate values that appear in H and I, which appear to be the formula replacing some (but not instances) of the letter with "a". For example, at B3643 and B3644 the values are 25523a_001.TIF and 25523b_001.TIF. In I8023 and I8024, the values are both 25523a_001.TIF.

I've attached the results with the duplicates highlighted.

(for the record, my brand new gaming desktop computer is now getting cranky at all of these files I have open. Who knew it would be Excel that brought it to its knees?)

metadataformatting2.xlsx