Excel VBA find and replace all characters before numbers

Question

In Excel, this is an example of the cell values. "▲99315","▲65". I'm trying to use VBA to remove the arrows which are always up or down, for gain or loss.

I can't seem to make this work using VBA. When I open this workbook it is auto updated. I would like to run a VBA to "Find and Replace" the arrows. When I try to paste the arrow characters in "find" it shows as a "?".

if they are in every cell, use text-to-columns to split the column into two and then delete the first column — jsotola, Sep 25 '17 at 18:41
Also can you include a screenshot, so we know exactly what arrows are being used? You may need to use the unicode character to remove them (an [example is here](https://stackoverflow.com/q/37024107/4650297)) — BruceWayne, Sep 25 '17 at 18:42
you can also use a formula, say the value is in A1: =MID(A1,2,LEN(A1)) assumes the arrow is always the first char. — Sorceri, Sep 25 '17 at 18:47

score 2 · Accepted Answer · answered Sep 25 '17 at 19:04

2

If all of those numbers compulsorily contain the first character as one of up and down arrows, you may give this a try....

The following code assumes that your numbers are in column A and start from Row2. If not, change it as per your requirement.

Sub ReplaceArrows()
    Dim lr As Long
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    Range("A2:A" & lr).Value = Evaluate("=IF(A2:A" & lr & "="""","""",RIGHT(A2:A" & lr & ",LEN(A2:A" & lr & ")-1)+0)")
End Sub

answered Sep 25 '17 at 19:04

Subodh Tiwari sktneer

9,906
2
18
22

This worked for me. Thanks for your quick help. First time here and had many quick answers. Amazing. – sms Sep 25 '17 at 19:28
1

@sms You're welcome! Glad it worked. Please take a minute to accept the answer to mark your question as Solved. :) – Subodh Tiwari sktneer Sep 25 '17 at 19:29

score 1 · Answer 2 · answered Sep 25 '17 at 19:21

To use VBA Find, you need to determine the code for the character you want to replace. Assuming the arrows you are using are the same as you pasted into your question, the following code will work:

with myRange
    .Replace what:=ChrW(&H25B2), replacement:=""  'Up Arrow
    .Replace what:=ChrW(&H25BC), replacement:=""  'Down Arrow
end with

Note the use of ChrW for the double byte character, and the Hex Code representing the up and down arrows.

score 0 · Answer 3 · answered Sep 25 '17 at 19:18

0

if these are conditional formatting icons you need to use the rng.FormatConditions.Delete method.

answered Sep 25 '17 at 19:18

GSD

1,252
1
10
12

Excel VBA find and replace all characters before numbers

3 Answers3