Linking cells for unique ID

[CoSinus]

Hello, everyone,
I would like to create a column for a database query whose content serves as a unique ID.

For this purpose an empty column A is to be inserted into the Excel table.
The contents of all following columns should be linked until there are no more columns with contents.
The separator between the chained cell contents is a hyphen.

The value in column B is to be taken over completely no matter what is in it.
Starting from column C the values should be taken over as follows:

If there is a number in front of the letters, it will be omitted.
If there is a number after the letters, whether with space or directly, the remaining cell content from and including the first letter should be transferred.
If there is only a number without letters in the cell, it will be completely ignored.

Summarized this means:
All letters will be taken from the first letter on, no matter what comes after it.
Numbers in front of the letters are omitted.

Hopefully the picture example will make it ab bit clearer.
The figure shows what the finished column A looks like.
Columns I and J are notes.

ID2.png


I hope you can help me.
Thanks a great lot.

[Keebellah]

Hi there,
What about extra spaces and . in the cell? Ignore these too?
I would also suggest all uppercase as ID and yes, it can be done probably with a formula or else using a macro but I don't know is this last option is an option for you.
You could write a UDF that does all the work for you

[CoSinus]

Thank you for your answer.
the extra spaces and . should all be copied. Everything should be copied after and including the first letter in the cell.

I would prefer a VBA but I am also very greatful for a formula.

Thank you very much for asking.

[Keebellah]

Will see what I can do for you

[jindon]

Based on your attachment.
UDF
Use in cell like
A2
=GetID(B2:H2,"-")

Please Login or Register  to view this content.

[Keebellah]

Nice code, looks good

[CoSinus]

Hi jindin,
thank you it works perfectly.
I hope you don't mind me saying that it works with
=GetID(B2:H2;"-")

Thank you and thank you again.
You are awesome

[jindon]

I hope you don't mind me saying that it works with
=GetID(B2:H2;"-")

I don't care.
It's just a matter of your regional settings.



If that takes care of your original question, select Thread Tools from the menu link above and mark this thread as SOLVED.

[CoSinus]

Hello, jindon,
the VBA works great and I have already handled about 15 workbooks.

However, my quad-core can't do this above 100 lines.
I also don't know if Excel supports Quad-Core.
Anyway, he has been working for 3 hours on an Excel file with 350 lines and he can't finish it.

Is it possible to have a formula for these large tables.
Since I don't have many of these tables, I can enter the formula manually.

Thanks again.

[jindon]

My old little PC calculates more than 5K lines without any problem, so no idea.

[CoSinus]

IŽll try another PC.
Thanks for the tipp.