Formula help

[excelesha]

Hello, please I am trying to replace the "4" that comes right after the lettes to "5", but the position of the 4 changes depending on the number of letter in front of it:

Unit 4	Unit 5
27BFWW4701	27BFWW5701
27BD4514	27BD5514
27AEUEA4214	27AEUEA5214

I have about 2000 rows that I need to modify to "5". The thing that is consistent is that the 4 comes right after the letters. Thank you in advance.

 

[excelesha]

Based on your examples, the first 2 characters do not have a 4.
So enough with this:

Dante Amor
	A	B
1	Unit 4	Unit 5
2	27BFWW4701	27BFWW5701
3	27BD4514	27BD5514
4	27AEUEA4214	27AEUEA5214
5	27ADE-4546	27ADE-5546
Hoja4

Cell Formulas
Range	Formula
B2:B5	B2	=SUBSTITUTE(A2,"4","5",1)

wow, works amazingly! Yes, haha, so far I did not run into a case with a 4 in the first two characters.

Thank you SO MUCH AGAIN.

 

[excelesha]

The formula also considers in case there is no 4. Try this

Dante Amor
	A	B
1	Unit 4	Unit 5
2	27BFWW4701	27BFWW5701
3	27BD4514	27BD5514
4	27AEUEA4214	27AEUEA5214
5	42AS123	42AS123
6	42AS432	42AS532
7	27BD4604	27BD5604
Hoja4

Cell Formulas
Range	Formula
B2:B7	B2	=LEFT(A2,2)&SUBSTITUTE(MID(A2,3,99),"4","5",1)

The change is small, just add this part:
=LEFT(A2,2)&SUBSTITUTE(MID(A2,3,99),"4","5",1)

@DanteAmor , you know what, I noticed this formula works too, even if there is a "-" in the characters.