VBA : Remove if

[HatchetHarry]

Hello guys !

I try to find a way to remove "#" if the next character is alphabetic and do nothing if it's a "number".
For example :
#Blue = Blue
#5 Green = #5 Green

I came up with something but it has no effect :

For i = 2 To 300
If Mid(Cells(i, 8), 2) = "[a-zA-Z]*" Then Cells(i, 8) = Replace(Cells(i, 8), "#", "")
End If

File here https://we.tl/t-y9ooeyp16l

I want to precise that all the data are string including numbers (from a previous code) that's why "not isNumeric" wouldn't work.

If someone has a clue...

Thank you very much,
Harry

 

[Rick Rothstein]

File here https://we.tl/t-y9ooeyp16l

Can you put your file on a site that does not require us to agree to the websites privacy and cookie policy? Try using DropBox (it's free and safe)...

www.dropbox.com

 

[Peter_SSs]

Does this do what you want?

Sub RemoveHash()
  With Range("H2:H300")
    .Value = Evaluate(Replace("if(%="""","""",if(abs(77.5-code(upper(mid(%,2,1))))<13,replace(%,1,1,""""),%))", "%", .Address))
  End With
End Sub

 

[Rick Rothstein]

More than likely, Peter has guessed at your data layout correctly in Message #3 , but I have some questions anyway...

1) Can there be more than one # sign in a single cell?

2) If not, is the # sign always the first character in the cell?

3) Is it possible single cell might not have any # signs in it?

4) Is it possible for a # sign to be followed by a non-alpha, non-numeric character and, if so, what should happen to the # sign?

 

[Ashutosh Kumar]

Hi,
You may use Isnumber with value formulae.
The below formulae works in excel as desired.

=IF(ISNUMBER(VALUE((MID(H2,2,1)))),H2,SUBSTITUTE(H2,"#",""))

 

[HatchetHarry]

@Peter_SSs, yes it works ! Unfortunately I realized that I forgot to gave you one more case. I have also data without "#" and the code you provided me delete the first letters of these. My bad, I linked a new file at the bottom.
@Rick Rothstein :
1) Can there be more than one # sign in a single cell? No

2) If not, is the # sign always the first character in the cell? Yes

3) Is it possible single cell might not have any # signs in it? Yes

4) Is it possible for a # sign to be followed by a non-alpha, non-numeric character and, if so, what should happen to the # sign? No - you will only find after "#" a number or a A-Z.

[/COLOR @Rick Rothstein, @Peter_SSs : I'm linking a new file file with all cases : https://www.dropbox.com/s/4o9293ceeelur3c/remove if v2.xlsx?dl=0
@Ashutosh Kumar : this formula works exactly like I want. Thank you very much - I will use this in case I dont find a VBA code to automate (because I use a template where I copy paste columns from a workbook to another)

Thank you all !
Harry

 

[Rick Rothstein]

Here is a macro that should work for you...

[table="width: 500"]
[tr]
	[td]Sub RemoveHash()
  With Range("H2:H300")
    .Value = Evaluate(Replace("IF((LEFT(@)=""#"")*(NOT(ISNUMBER(0+MID(@,2,1)))),MID(@,2,LEN(@)),IF(@="""","""",@))", "@", .Address))
  End With
End Sub[/td]
[/tr]
[/table]

 

[HatchetHarry]

@Rick Rothstein, It works perfectly thanks a lot really appreciate your help !!!