Split a fixed format column cell into 3 separate columns

%3CLINGO-SUB%20id%3D%22lingo-sub-3406381%22%20slang%3D%22en-US%22%3ESplit%20a%20fixed%20format%20column%20cell%20into%203%20separate%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3406381%22%20slang%3D%22en-US%22%3E%3CP%3EMy%20financial%20download%20has%20account%20ID%20as%201%2010%20digit%20field%3A%26nbsp%3B%20Example%3A%26nbsp%3B%201234-11-22%3C%2FP%3E%3CP%3EI%20want%20to%20know%20if%20there%20is%20a%20way%20to%20divide%20that%20account%20ID%20into%203%20separate%20field%20segments%20based%20on%20fixed%20format%3A%3C%2FP%3E%3CP%3EExtract%201234%20into%20separate%20field%3B%2011%20into%20separate%20field%3B%20and%2022%20into%20separate%20field.%26nbsp%3B%20I%20want%20to%20sort%20and%20do%20subtotals%20on%20each%20of%20the%203%20segments%20for%20reporting%20financial%20data.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3406381%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3406674%22%20slang%3D%22en-US%22%3ERe%3A%20Split%20a%20fixed%20format%20column%20cell%20into%203%20separate%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3406674%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1396069%22%20target%3D%22_blank%22%3E%40Herbie855%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20assume%20that%20these%20IDs%20are%20in%20a%20column.%3C%2FP%3E%0A%3CP%3EMake%20sure%20that%20there%20are%20two%20empty%20columns%20to%20the%20right%20of%20it.%3C%2FP%3E%0A%3CP%3ESelect%20the%20IDs.%3C%2FP%3E%0A%3CP%3EOn%20the%20Data%20tab%20of%20the%20ribbon%2C%20click%20Text%20to%20Columns.%3C%2FP%3E%0A%3CP%3EIn%20Step%201%20of%20the%20resulting%20dialog%2C%20select%20Delimited%2C%20then%20click%20Next.%3C%2FP%3E%0A%3CP%3EIn%20Step%202%2C%20click%20in%20the%20box%20next%20to%20Other%20and%20type%20a%20hyphen%20-.%20This%20will%20automatically%20tick%20the%20Other%20check%20box.%3C%2FP%3E%0A%3CP%3EClick%20Finish.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

My financial download has account ID as 1 10 digit field:  Example:  1234-11-22

I want to know if there is a way to divide that account ID into 3 separate field segments based on fixed format:

Extract 1234 into separate field; 11 into separate field; and 22 into separate field.  I want to sort and do subtotals on each of the 3 segments for reporting financial data.

1 Reply

@Herbie855 

I assume that these IDs are in a column.

Make sure that there are two empty columns to the right of it.

Select the IDs.

On the Data tab of the ribbon, click Text to Columns.

In Step 1 of the resulting dialog, select Delimited, then click Next.

In Step 2, click in the box next to Other and type a hyphen -. This will automatically tick the Other check box.

Click Finish.