How to Separate Chunks of Characters and put them in separate Columns

Hello All, I need to create a program wherein I will be reading from a text file and then I'll print the characters in the Excel sheet. I can basically read and write to the excel sheet.

But there are certain fields where the details must be put.Problem is, I have a lot of characters that are chunked together which makes it hard for me to determine which details goes into which field.

To make it clear,
In my Excel sheet I have 3 Fields namely,
Column A = ID number (max of 8 characters)
Column B = Name (max of 10 characters)
Column C = Description (max of 20 Characters)

If the maximum length of characters are not all used up, then I can put spaces in it.

So in my text file that I will read, It has:

10827835Crisshinn QWERTY

wherein:
10827835 = ID number
Crisshinn = Name
QWERTY = Description

Notice that there is 1 space between "Crisshinn" and "QWERTY", this is the number of spaces that was printed in the textfile in order to complete all 10 characters.

However, the "10827835" and "Crisshinn" part are chunked together. Since the ID number detail must always have max of 8 characters. Therefore, it will always be chunked together with the Name detail. So How can I separate them and display it in the 2 columns?

I know I need to count the characters but I'm not too familiar on what function or how I should use it. Any help is greatly appreciated. Thank you :D

Will the ID always be 8 characters then?

Dom

I've attached an example for either fixed or variable ID length. For a variable ID it uses a UDF to extract the numerical digits, assuming the only numbers in the string belong to the ID.

Dom

Or maybe without VBa

Assuming your data begins in A2

Try this array formula in B2 (This will extract up to 10 digits from the string)

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter

in C2

Please Login or Register  to view this content.

in D2

Please Login or Register  to view this content.

Drag/Fill all three down to suit

Hope this helps

Originally Posted by Domski

Will the ID always be 8 characters then?

Dom

I'm sorry there's one part I got wrong. Actually, NO. The maximum number of digits for the ID is 12. So if all 12 characters are not used, then spaces must be printed to complete the 12characters. So sorry about that. Thats why I can't use your =Left(A2,8) because if I increase the range, it will also include the Characters.

I am inclined to use the =ExtractDigits(A1) function although... I need to increment the Range so that I can loop it throughout the excel. So I can't really put A1 because it's too specific. I was thinking of using the line Cells(x,y) as the range, so that I can just increment x and y, but I can't combine it with the ExtractDigits function. ;(

As it's always 8 characters then the example in row 2 which doesn't need the VBA udf will suffice.

For info though the function uses what are known as Regular Expressions to extract just the numerical digits from the string. I'll admit I don't really understand their full power myself and but have seen some very clever things done with them.

Option Explicit is just an option in the VBA editor that requires you to declare any variables that you use within your code. There aren't any used in the code in the example, it's just a setting I always have on and would recommend everyone does. You can find some more info on it here.

Dom