Repeat Replace Function

[vbaInet]

So if there's no abbreviation in the table, you want the full name returned too?

 

[welcomesui@gmail.com]

So if there's no abbreviation in the table, you want the full name returned too?

ID   |   Field4                         |   Required
----------------------------------------------
1        ICI Bank,ADR, pfd              ICI Bank,American Dep Rep, Preferred Stock       
2       Man Bank, pfd                   Man Bank, Preferred Stock
3       Yes Bank                          Yes Bank

Here
ADR - American Dep Repe
pfd - Preferred Stock


if nothing abb is there it should return filed4

 

[vbaInet]

Inconsistency in your data then.

 

[welcomesui@gmail.com]

Inconsistency in your data then.

the sample data provided as tested data with two ways.. from name to abb and abb to name.

if the filed4 contians any abb those abbreviations are need to modify with new name, if no abbreviations are found, keep the old data.

I think no inconsistency my data.

 

[vbaInet]

The code is using ", " (with a space) as the delimeter. Your data has "," and ", " so you have three options:

1. Change the code to use "," (without space) as the delimeter
2. Perform two replace opertations, one for "," and the other for ", " and join both together.
3. Amend your data so it's consistent.