Video instructions and help with filling out and completing Who Form 1094 B Calculator

Instructions and Help about Who Form 1094 B Calculator

Welcome to excel metric number one one one three if you want to download this workbook 1130 to 1115 click on the link below the video in this video we have a column filled with data and we need to break it apart so every single character goes into a single cell one character for each cell now we're going to do this two ways text to columns fixed width and then we'll see how to do it with a formula now the formula is important if you had data dumps regularly and you wanted to automate the process so it's just automatically every time you change the data here it updates ah but text to columns pretty darn easy if it's a one-time affair now the only trick I see or the only problem I see is if you have a huge column since the dialog box we're actually going to have to draw lines for fixed width I might want the longest one at the top so I see it at the top of the dialog box so I'm going to give a field name Len control enter control B enter and then use the Len function the levels just tell you how many characters are in that cell double click and send it down and then I can right click on a single cell in that column sort largest to smallest so the biggest one is at the top and then I could delete this I'm going to move this way off to this side just to keep it there as a trail now I can highlight and text the column data text to column or alt AE L to e also delimited we don't have a character that separates what we want to split we have fixed width we go to next and here it is watch this this is tedious but we can just click click click and draw our lines all right there we go I'm clicking next because I'd like to send this to the next cell I want to keep the source data so I'm going to change in text 3 the destination when I click finish but look just like that that is beautiful one time affair but if you need it dynamic you know the formula for doing this is not too hard let's go ahead and look first I need to extract them to use the mid function I'm going to say start at the first one then the second one then the third one so as I copy the formula this way I need a formula element to count one two three four or create sequential numbers so I'm going to use the columns function now I'm sitting in C 19 so watch this dollar sign to lock the C and then 19 : C 19 columns counts how many columns C to C how many columns are there one because the second one is not locked and the first one is that will expand as we go to the side so if I copy this all the way over at any point you could see it's expanding C to L there are ten oh I can see I highlighted it in the wrong direction but nevertheless I can copy these all the way down and in each cell I get my sequential numbers now I'm going to use the mid the mid extracts sub text strings from a larger text string the text is right there now as I go to the side I need to lock it but as I copy down they needed to move so I need to lock the a F for three times lock the a but not the 19 that means the whole way over it'll look at that data but when I copy it down one I'll be looking at the next bit of data where's the start number 1 2 3 4 5 and number of characters is always 1 close parentheses control enter copy it over and then double click and send it down now look at that that works perfectly when we get over here it's not it can't find anything there isn't any whatever position that is so it extracts perfectly if I were to change this to whatever instantly it updates control Z all right formula icon like that one that was pretty easy I didn't have to draw all those lines but if it's a one-time deal text to columns is pretty cool all right we'll see you next video