Macro loop: Copy value down until it meets next number

Hi, I am new to this and wonder if you can help.

I have a text file which is generated from a mainframe computer. I need to sort the data but since there is no identifyer, I need to create a column, copy the name (highlighted in the yellow lines) from the cells that start with 'ACCESSORID = BSaaaa NAME = AAA'. Note that the 'aaaa' are numbers and will be different for each record, and so as 'AAA'.

The name can be extracted from these lines and be taken from the characters after 'NAME ='

So hopefully, I can use a macro to create a new column (say A) and copy the cells with names down in the new column (the name will change at every few lines when the record in the adjacent column changes.)

Thanks very much in advance.

Thanks for your prompt reply. Unfortunately, this is not what I need. I have added a page 'Results' which is what I am after. Thanks.

Thanks so much. This is it. Can you let me know whether this will work if the file has many more records? Thanks.

hi bone123, formula option

thanks. you are right, the names will have different number of charcters. actually the name format is xxx,uuu

Thanks, this is a great and simple solution to my problem. I do not quite understand the formula though. Do you mind telling me what it actually does? Thanks.

the formula does the following:
1. Looks for "NAME =" string
2. If it's not found, the formula result equals to the value of one cell above
3. If it's found, position of the string + 13 (length of "NAME =" + 1 symbol) gives the start position of the string after "NAME =", it will take any length string

Thanks very much.

Thanks very much for the 2 solutions provided. It is actually only a start. With that, I managed to manually manipulate the data so that I have a column with names and a column with profiles.

Separately, I have 2 more mapping tables. One is a table of model profiles that tells me which profiles under the model profiles and another table that tells me who should be under which model profiles.

I spent a few hours to manually check to make sure that people under the respective model profiles have been set up properly. What I did was to pivot the information to have the names in the columns and profiles in the rows. Then I had to manually match out the ones in the model profiles and color code them.

In the end, I just investgated the items that are not color-coded.

That sounds complicated. However, my current question is a very simple one. Do you think it is possible to automate the above using Excel or will I have to use Access?

Thanks.