VBA : Remove if

HatchetHarry

New Member
Joined
Aug 20, 2018
Messages
16
Office Version
  1. 365
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 :
Code:
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Does this do what you want?

Code:
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
 
Upvote 0
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?
 
Upvote 0
@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
 
Last edited:
Upvote 0
Here is a macro that should work for you...
Code:
[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]
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top