VBA code for extracting last three digits from alphanumeric string.

Can you assist me with a code to extract the last three digits from this alpha string AKAKAK44000XXXXXXX164. I do not want the =Right function.....I want a VBA Code.

then after I extract the last three digits I would like the code to also include a replacement macro that will read something like:

Cells.Replace What:="164", Replacement:="ABA", LookAt:=xlPart, SearchOrder:=xlByRows

It's pretty simple and I can do it using a worksheet function but I want something more efficient.

Thank You.

This isn't how I would normally do it but it since you asked for assistance I figured I would incorporate what you already came up with

Please Login or Register  to view this content.

Try this on a copy of your workbook

Please Login or Register  to view this content.

You might need to change "UsedRange" with something like

Please Login or Register  to view this content.

Thanks for including my work. I'm new to this and still learning. I like your code because I understand it clearly. It works perfectly. But, how do I make the replaced values show up in another column.

Its not working. I get a 400. I changed the used range.

The alphanumerics are in column A I would like the replace results to show in column B.

Yes, it's great the only thing is I want the Column C to contain only the replacement text and none of the alphanumeric code....just the replacement text. Thanks for you time!

Syntax error @ c.Offset(,1),Value = strReplaced

Are these 30 variations with their numbers on a different sheet and if so where on that sheet and what is that sheet's name?
Don't you want the "ABA" to replace the three digits anymore?

You could try this if the variations with their respective 3 digit numbers are not in your workbook.
You'll have to change and expand both Arrays (arr1 and arr2) to reflect these variations and 3 digit numbers.
The variations have to be indexed (in the same place) as the corresponding 3 digit numbers.
If you look at the code, that means that 532 corresponds with Compliance, 425 with National etc etc.
When you have changed the arrays you should have the same amount of entries in arr2 as you have in arr1.

Please Login or Register  to view this content.

Got it!

I updated the array to match the desired replacements. I couldn't correct the string to pull the last three digits into another column but I could get it to leave it in the remaining column and it works! I didn't understand the Application match potion of the language but left it iand it works nicely. Thank You! and All the BEST!

I don't know how large the range you're working with is but if you use formulae, it should be considerably faster.
On my old computer, using formulae is about 5 times faster witha 16,000 cell range.
If you'd like to try that you should attach a stripped down version of your workbook.