How to search a string and split it?

zerfetzen

New Member
Joined
Mar 2, 2006
Messages
6
Hi all,
I am using Excel for statistics. Each column contains descriptive statistics on a variable, including its name (let's say, row 1, and the current value of the variable in row 2). I'm trying to program it to search from left to right through the variable names for a variable name with _x_ in the middle of it. For example, it may find a variable called Age_x_Sex. When it finds a _x_, it should search for the variable name before it (Age) and after it (Sex), to find these variable locations. Once I have the locations, I can have it multiply the values of the variables together, reproducing the interaction effect.

Here's what I have so far:

Sub Main()
Dim CurCol As Integer 'Current Column
Dim TempVar1 As String
Dim TempVar2 As String
Dim TempVar3 As String
Dim Var_Num 'Number of Variables

For CurCol = 0 To Var_Num
TempVar1 = Range("A1").Offset(0,CurCol)
'Non-code begins
'Search TempVar1 for "_x_"
'Assign string prior to _x_ to TempVar2
'Assign string after _x_ to TempVar3
'Search list of variables for TempVar2, store its column location (like G)
'Search list of variables for TempVar3, store its column location (like M)
'Non-code ends
Range("A2").Offset(0,CurCol) = "=" & TempVar2 & "2*" & TempVar3 & "2"
Next Var_Num
End Sub

I'm new at searching strings. Help? Thanks.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Forum statistics

Threads
1,136,650
Messages
5,676,996
Members
419,667
Latest member
MegEri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top