Excel vba: extract characters before delimiter in active cell

[2Took]

Hi,

Not looking for a Function.

1. Need VBA to extract left characters before delimiter "." in active cell and call it ExL
2. Need VBA to extract right characters before delimiter " - " in active cell and call it ExR

Got code below, but those are functions, don't need that:

If InStr(txt, char) > 0 Then Extract_LString = Left(txt, InStr(txt, char) - 2)

If InStr(txt, char) > 0 Then Extract_RString = Right(txt, InStr(txt, char) - 4)

 

[johnnyL]

How about:

ExL = Split(ActiveCell, ".")(0) ' Get characters to left of the delimiter '.'
ExR = Split(ActiveCell, " - ")(1) ' Get characters to right of the delimiter ' - '

Change the delimiter to what you want.

 

[2Took]

How about:

ExL = Split(ActiveCell, ".")(0) ' Get characters to left of the delimiter '.'
ExR = Split(ActiveCell, " - ")(1) ' Get characters to right of the delimiter ' - '

Change the delimiter to what you want.

Thank you, but now I am getting below error...
Trying to use ExL & ExR to filter in corresponding column in row 10, where I have filters.
Running your code above on a cell which value looks like this:

H. HeaderName - SearchTerm

 

[johnnyL]

ExL is a variable ... Remove the quotes from around it.
The same goes for ExR

 

[2Took]

ExL is a variable ... Remove the quotes from around it.
The same goes for ExR

Removed quotes in both cases, but still get this error:

 

[johnnyL]

You are trying to filter a single cell.

 

[2Took]

You are trying to filter a single cell.

No, when I go to the same address manually and filter - multiple results come up. If that's what you mean...

 

[johnnyL]

Your line of code equates to:

ActiveSheet.Range("H10").AutoFilter Criteria1:="Search Term"

Filter requires a range of cells, you have 1 cell.

 

[2Took]

Your line of code equates to:

ActiveSheet.Range("H10").AutoFilter Criteria1:="Search Term"

Filter requires a range of cells, you have 1 cell.

ok, so what code do I need to fix that?

 

[johnnyL]

What range are you trying to filter? H10:H20 for example, or All of column H?