Wild card to find but not replace...

[YeshyTiger]

Good Morning,

I looked but didn't find another thread that addressed this question. If there is a post please direct me...

The question in short: Is it possible to use the wildcard function to find errors in an Excel workbook but then keeping the values of the wild card when replacing the error.

In long: I'm given a number of Excel data sheets to import regularly into another program but first I must check for the data to be in correct format. For example, data tagging is done in heirarchal order so a sample set might be : "A" then "A.1" and so on. Errors vary widely but 2 consistent problems are "A " and "A 1" . Because of this I cannot simply replace " " with "." I was hoping I could do a replacement code of find "* *" and replace with "*.*" In other words, keeping the values but replacing the error

Is this possible? Thank you

 

[Scott Huish]

I don't think you can do this with Find and Replace directly, but it could be done with a formula:

=SUBSTITUTE(TRIM(A1)," ",".")

then you could do a Copy, Paste Special Values and delete the original column if you like.

Or with VBA, which could do it in the same column

Sub test()
Dim c As Range
For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    c = Replace(Application.WorksheetFunction.Trim(c), " ", ".")
Next
End Sub

I used the Worksheet function Trim rather than the VBA function Trim so that it would also remove excess spaces within the string.

 

[YeshyTiger]

Thank you for the reply

I would prefer the VBA as I'm in Mid-development on a macro that will check and fix all common errors in all the .xls in a folder.

Will I need to end the Dim if I input this code into a larger macro?

 

[Scott Huish]

There is no such thing as End Dim
if this is what you mean

 

[YeshyTiger]

Ok, I think I understand. I'm sorry, I'm fairly new to Excel VBA and taking on a project probably a bit bigger than I am at the moment.

So using that code, could I also search for and eliminate other errors such as "A.." which should be "A" or "A..1" which should be "A.1"?