Replacing the first part of a cell with a different value

[papasmurfuo9]

Hi,

I would like a macro to replace the first 3 digits of a cell depending on the value, with 2 digits.

i have a list of account numbers where the first 3 digits need to be changed depending on what account they are for
example -

802-123456, 802 refers to the area,
803-123465, 803 refers to the area,

then i have a list of 2 digits that refer to the area, that i need to use
example -
802-123456, the area code for that i need to use is 25 so i need the macro to run through column a
and replace the 802 with 25, so it would read 25-123456

then for 803, i need the 803 replacing with 26 and so forth

Please could someone point me in the right direction in acheiving this.

Many Thanks

[papasmurfuo9]

Anyone help with the above?ty

[nilem]

Hi papasmurfuo9,
maybe something like this

Please Login or Register  to view this content.

[papasmurfuo9]

seems to work perfectly thank you.

[papasmurfuo9]

Hi Nilem
one little problem, if the 802, is in the account number, example 802123802, it will replace the 802 at the end too, is there any way to get it to just look at the first 3 digits?

thank you

[nilem]

in your example was 802-, and it is replaced by 25-
or show an example of a file with the options on the account number

[papasmurfuo9]

Yes sorry i was just trying to make it more clearer in my explanation
there is no "-" so it would read 802123456
your code works perfect in replacing the 802, into the correct number for me
but if by chance the account number has another "802" somewhere in it, it replaces that too
i would like if possible to just change the first 3 digits of the cell, and ignore the rest.
this possible?
thank you.

my code is now

Please Login or Register  to view this content.

[nilem]

If you do not have a lot of data, you can try so

Please Login or Register  to view this content.

[papasmurfuo9]

great - thank you.

[Chriz]

Just thought I'd add something else into the mix papasmurfu09 - you could also use the left and right features in a loop if the account numbers will always stay the same amount of characters, so something like:

Please Login or Register  to view this content.

Could probably work for you too, it looks for the first three characters and replaces them with whatever you want to replace it with and then adds the last 6 characters that need to stay in there to create the final number.

Should just point out I didn't have time to enter all of your values but you will just need to add in the additional else ifs etc.

Thanks

Chriz