Replacing variable/column prefixes and suffixes

[iotapsi321]

Hi, everyone. This is such a wonderful resource, glad I was able to find it. I am a beginner to excel so this may be an easy question for most of you. I have hundreds of variables that need to be renamed. Each variable has a prefix and a suffix. The variables are sequentially ordered in batches according to their keycode, but the variables are randomly ordered according to their suffixes. For instance, a variable named xaa.100 in column A1 needs to be renamed to time1_age. A more thorough example is provided below. There are 35 unique prefixes, and unique10 suffixes, so a simple find and replace all would take quite a bit of time. Is there a formula that can tackle this problem more efficiently? Thank you in advance for your help!

FROM

xaa.100

xaa.101

xaa.102

bzb.101

bzb.102

bzb.100

nrf.102

nrf.100

nrf.101

TO

time1.age

time1.gender

time1.race

time2.gender

time2.race

time2.age

time2.race

time2.age

time2.gender

 

[steve the fish]

This piece of code will do that to the active sheet when its run:

arr = Array("xaa", "time1", "100", "age", "bzb", "time2", "101", "gender", "nrf", "time2", "102", "race")

With ActiveSheet.UsedRange
    For i = LBound(arr) To UBound(arr) - 1 Step 2
        .Replace arr(i), arr(i + 1)
    Next
End With

 

[iotapsi321]

Thanks. I’m a beginner so I just want to make sure I understand correctly. Do I just enter this code into any cell within the active sheet?

 

[steve the fish]

To be clear are these constants in the cells or as the result of formulas?

 

[iotapsi321]

To be clear are these constants in the cells or as the result of formulas?

They are constants. Just alphanumeric text across row A.

 

[steve the fish]

Ok so this is VBA code ive given. You need to open the workbook and if you need this code more than once then you need to first save the workbook as macro enabled workbook. Open the macro enable workbook and then go to the sheet where you want the replace to happen. Press ALT and holding it down press F11. Press insert then module. Into there paste this:

Sub ReplaceMacro()

arr = Array("xaa", "time1", "100", "age", "bzb", "time2", "101", "gender", "nrf", "time2", "102", "race")

With ActiveSheet.UsedRange
    For i = LBound(arr) To UBound(arr) - 1 Step 2
        .Replace arr(i), arr(i + 1)
    Next
End With

End Sub

Close the VBA editor. Staying on the page where you need these replacements go to the view menu then macros. Run the ReplaceMacro macro. They should have been replaced. It works by the above array in the code pairing the find what and replace with values. eg xaa will be replaced with time1, 100 will be replaced with age etc etc.

 

[iotapsi321]

Thank you so much! The code worked. I did run into an issue where the code for one line was so long that I had to carry it over another line, but hitting the enter button did not work, as I received an error message. Is there a quick fix for this? Thank you again for your time.

 

[iotapsi321]

Nevermind. I figured it out. Thank you again (and to everyone on this website)!