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.

 

[DanteAmor]

Hi @excelesha. Thanks for posting on the board.

If the letters always start from the third position, then:

Dante Amor
	A	B
1	Unit 4	Unit 5
2	27BFWW4701	27BFWW5701
3	27BD4514	27BD5515
4	27AEUEA4214	27AEUEA5215
5	42AS123	42AS123
6	42AS432	42AS532
Hoja4

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

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[excelesha]

Thank you so much for replying!

The formula doesn't work if i have another 4 in the cell, see below:

27BD4604>>>>changes to 27BD5605, but I need 27BD5604

 

[Fluff]

Assuming you will never have a 4 before the letters, how about

Fluff.xlsm
	A	B
1	Unit 4	Unit 5
2	27BFWW4701	27BFWW5701
3	27BD4514	27BD5514
4	27AEUEA4214	27AEUEA5214
Main

Cell Formulas
Range	Formula
B2:B4	B2	=REPLACE(A2,FIND("4",A2),1,5)

 

[Fluff]

If you can have a 4 as part of the 1st 2 characters, use

Fluff.xlsm
	A	B
1	Unit 4	Unit 5
2	27BFWW4701	27BFWW5701
3	27BD4514	27BD5514
4	27AEUEA4214	27AEUEA5214
5	24AEUEA4214	24AEUEA5214
Main

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

 

[DanteAmor]

The formula doesn't work if i have another 4 in the cell, see below:

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)

 

[excelesha]

OMG. you guys are AMAZING, @DanteAmor @Fluff , both formulas work! This is total genius, i thought it wouldn't be possible! Thank you so much!!!! Made my work so much faster and efficient.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[excelesha]

OMG. you guys are AMAZING, @DanteAmor @Fluff , both formulas work! This is total genius, i thought it wouldn't be possible! Thank you so much!!!! Made my work so much faster and efficient.

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)

Thank again.

Is it possible to modify the formula further to include instances with 27ADE-4546 to be changed to 27ADE-5546, so basically the alphabets have a - then the 4 which is to be changed to 5. There are many cells in between that have a - after the alphabets.....

27BFWW4701	27BFWW5701
27BD4514	27BD5514
27AEUEA4214	27AEUEA5214
27ADE-4546	27ADE-5546

 

[DanteAmor]

so basically the alphabets have a -

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)