Data extraction

[ssim]

I am trying to find an easy way to add leading zeros to cell (actually a whole lot of them)

I have imported about 55,000 lines of data and one I extracted the unique row number which I always thought had been numbered with a six digit number. However, my predecessor did some 4 and 5 character numbering. The row that I have these numbers extracted to is formatted as text. I have had some success in sorting by this number and then in another column enter either 0 or a 00 and then use CONCATENATE to join the original number to come up with a new six digit with the appropriate number of leading zeros. I am crying uncle and asking for help as the method that I am using is time consuming every time I need to do a new import from the database.

Column G = 2625_Feb06_Banff
Column I = 2625
Column J = 00
K = 002625


Column G is where the original data is, I the digits I extracted, J the added leading zeros and K is where they are put together.

I have tried to use the LEN along in an IF statement so but I can't seem to get it working. And while I have someone's attention, I am ok on extracting from either the beginning or end of a string of data but I am having trouble extracting something that might reside in the middle such as this.

YULngt_051105_1001 where the 051105 is what I am after and then I would want to get the above to work as well. In this case it is 6 digits but it is not always so. I know people will cringe at this but I have been handling these ones manually and it takes hours.

I am attaching a small spreadsheet with sample as it is now and included only the data that is important to this question.

Thanks much

[martindwilson]

try
=TEXT(LEFT(G3,FIND("_",G3)-1),"000000")
alternatively if its always the first set of numbers in a given string
=TEXT(LOOKUP(9.99E+307,--MID(G3,MIN(FIND({1,2,3,4,5,6,7,8,9,0},G3&1234567890)),ROW(INDIRECT("1:"&LEN(G3))))),"000000")
that will work with YULngt_051105_1001
as well