Erase last start together with last characters

[bjelinski]

Hello,

I have following problem. I have data that represents certain ID CODE. This ID CODE has some unnecessary characters. The last star with letters (i.e. *DG or *HSL etc. ) should be removed. I couldn’t use any other typical excel functions like “left “and “Len”, because the data gets updated thru Access database. So there must be some code that would make the process automaticly.

In attachment I have included a mock up of the sample data. The important part is ID CODE column. As you can see there are more stars in the code, but the relevant part is to erase the last star together with the letters after the star.
Example: This ID CODE - 63047450201*RO*Kolis*RBBG*LIM would become THIS 63047450201*RO*Kolis*RBBG

Thank you in advance,
Bjelinski

[Bob Phillips]

Please Login or Register  to view this content.

[bjelinski]

thanks , this worked great! anyway because i want to understand it little bit deeper , can you tell me if I would like to tweak it/modify the code, and for example want to erase the part where you defined the column (“C”), by for example defining this part as the name of the column (in mine case “Code ID”), what would I need to add there to be able to define the column that needs to be changed by defining the exact name of the column (i.e. “Code ID”) and not defining it as column letter?

Sorry on stupid question,

But thanks for your help! Officially this has been solved.

[Bob Phillips]

thanks , this worked great! anyway because i want to understand it little bit deeper , can you tell me if I would like to tweak it/modify the code, and for example want to erase the part where you defined the column (“C”), by for example defining this part as the name of the column (in mine case “Code ID”), what would I need to add there to be able to define the column that needs to be changed by defining the exact name of the column (i.e. “Code ID”) and not defining it as column letter?

Sorry on stupid question,

But thanks for your help! Officially this has been solved.

Change the bits that say

"C"

to

.Range("CodeID").Column.

Or better, set a variable to that column value and use the variable.

[bjelinski]

Thanks, once again!

[bjelinski]

Sorry on bothering, I think I am doing something wrong?

I tried using the name, but I allways get the compiling error. This is probably because of mine lack of expertise in VBA.

I want to use this code in acces database, and probably for the database it would be better if I can define the column by exact name, but apparently I dont know how to do it

thanks in advance.