Delete 5th character in text string in range only when preceding characters are "Day "

[Ironman]

Hi

I12:I8669 contains text entries and I need to delete the 5th character in all cells in that range but only where the preceding characters are "Day " (Day + space).

What follows those characters is a zero (which I need deleting), followed by a unique number. I can't use search and replace to remove the leading zero as the characters "Day " can also appear later in the text string in some cells.

Hope you can help?

Many thanks!

 

[DanteAmor]

Try this:

Sub deletechar5()
  With Range("I12:I8669")
    .Value = Evaluate(Replace("=IF({1},IF(@="""","""",IF(LEFT(@,4)=""Day "",REPLACE(@,5,1,""""),@)))", "@", .Address))
  End With
End Sub

 

[Eric W]

Try:

Book1 (version 1).xlsb
	I	J
12	something	something
13	Day 012345	Day 12345
14	else	else
15	day 09999	day 9999
16	4567xBirthDay Party	4567xBirthDay Party
Sheet17

Cell Formulas
Range	Formula
J12:J16	J12	=IF(LEFT(I12,4)="Day ",REPLACE(I12,5,1,""),I12)

 

[Rick Rothstein]

@Eric... base of the OP's hint that it is a leading 0 that he wants to delete, I think changing your formula to this might be better (just in case there is no leading zero)...

=IF(LEFT(I12,5)="Day 0",REPLACE(I12,5,1,""),I12)

 

[DanteAmor]

In case you decide on a formula, here is a slightly shorter version. Also consider if the cell is empty, the result will be empty, which with Erick's formula will probably return a 0 if you have configured: Show a zero in cells that have zero value.

varios 16sep2021.xlsm
	I	J	K
12	Day 0123	Day 123	Day 123
13	Day 1234	Day 1234	Day 234
14	some	some	some
15			0
16	Day 999	Day 999	Day 99
Hoja2

Cell Formulas
Range	Formula
J12:J16	J12	=REPLACE(I12,5,IF(LEFT(I12,5)="Day 0",1),"")
K12:K16	K12	=IF(LEFT(I12,4)="Day ",REPLACE(I12,5,1,""),I12)

 

[Ironman]

Many thanks for your solutions gentlemen.

@DanteAmor: I ran your code and it unfortunately accumulated digits for the cells that didn't contain "Day 0". Your formula works as per Eric's (great minds think alike )
@eric: Thanks ever so much, your formula did the job with the leading zeros but unfortunately returned zeros for the other entries.
@Rick: Your formula worked perfectly - thank you ever so much!

Thanks once again for all your continued help and time gentlemen.

 

[DanteAmor]

I ran your code and it unfortunately accumulated digits for the cells that didn't contain "Day 0".

Try the updated code:

Sub deletechar5()
  With Range("I12:I8669")
    .Value = Evaluate(Replace("=IF({1},IF(@="""","""",IF(LEFT(@,5)=""Day 0"",REPLACE(@,5,1,""""),@)))", "@", .Address))
  End With
End Sub

Your formula works as per Eric's

You mean this formula, I think it gets the expected result.

=REPLACE(I12,5,IF(LEFT(I12,5)="Day 0",1),"")

 

[Ironman]

Try the updated code:

Sub deletechar5()
  With Range("I12:I8669")
    .Value = Evaluate(Replace("=IF({1},IF(@="""","""",IF(LEFT(@,5)=""Day 0"",REPLACE(@,5,1,""""),@)))", "@", .Address))
  End With
End Sub

That works perfectly now, thank you!

You mean this formula, I think it gets the expected result.

=REPLACE(I12,5,IF(LEFT(I12,5)="Day 0",1),"")

Yes, apologies, that works perfectly as well! Thanks again!