How to replace "," or "." from a value using VBA

[ThrottleWorks]

Hi,

I have two different worksheet in same file.
Both sheets has a column for name.

I need to find sheet 1 name in sheet 2
For example, sheet 1 has name such as 'Suzuki, motorcycles Ltd.'

Sheet 2 has name as 'Suzuki motorcycles'.
I need to find this names as contains, also I need to remove , and . from my string before doing a contains search.

I have a loop. This loop is based in the names in sheet 1.
I am trying to do contains search with this loop.

For each name in sheet 1
Find this name in sheet 2 using contains (xlpart)

At present, I have populated a helper column, replaced all , and . before doing search.

But I would like to know how do I to achieve this with VBA. I am trying to write something like

"Rng.Offset(0, 3).Replace What:=".", Replacement:="", LookAt:=xlPart"
In my loop. If RNG value contains , or . in string then replace those values from that string.

In my loop, I want to write something like, find name from sheet 1 in sheet 2 as contains. But before finding the name remove , or . from the string.


How do I do this, can anyone please help me in this.

 

[Chihiro]

You can use Instr() to check if "." or "," exists in string then use Replace().

http://www.techonthenet.com/excel/formulas/instr.php
http://www.techonthenet.com/excel/formulas/replace_vba.php

 

[ThrottleWorks]

Good morning @Chihiro sir, thanks a lot for the help. I am checking this and will revert with details.

Have a nice day ahead.

 

[Deepak]

Simply use replace on fly in array then search the same

For ref. Check a recent thred subjected Round the number where hui as suggest round use in a array then put it back to sheet or not.

Let us know to help you further...

 

[ThrottleWorks]

Thanks a lot.

 

[Marc L]

Hi !​

For example, sheet 1 has name such as 'Suzuki, motorcycles Ltd.'

Sheet 2 has name as 'Suzuki motorcycles'.
I need to find this names as contains, also I need to remove , and . from my string before doing a contains search.

I have a loop. This loop is based in the names in sheet 1.

Within a loop, no need to replace anything but just use Like operator !

If Range.Value Like "Suzuki*" Then …

Just see this operator in VBA help !
​

 

[ThrottleWorks]

Hi @Marc L thanks a lot for the help. Sorry for late reply. Am checking this. Will revert with details. Have a nice day ahead.