Get Part of Work Sheet Name

[Junarkar]

Hi,

I am trying to get part of active worksheet name by avoiding date part after underscore. I used below code;
Given Sheet Name is Computers_July 15

Range("K2").Value = Left(ActiveSheet.Name, Len(ActiveSheet.Name) - InStr(ActiveSheet.Name, "_") )

And it returned below result - Compute
Where required result is - Computers

The sheet name is dynamic. If the sheet name is short it will take extra characters (for ex. APSS_Jul insted of APSS). If the sheet name is longer I get less characters like the example given above.

How can I correct it.

Thanks in Advance.

 

[vcoolio]

Hello Junarkar,

Try it as follows:-

Sub Test()

Dim str As String: str = ActiveSheet.Name

Range("K2").Value = Split(str, "_")(0)

End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[vcoolio]

Thanks Hui!

 

[shrivallabha]

Your aim and code do not align. Your code should be:

Range("K2").Value = Left(ActiveSheet.Name, InStr(ActiveSheet.Name, "_") - 1)

Syntax you have written

Len(ActiveSheet.Name) - InStr(ActiveSheet.Name, "_")

is more useful to take remainder part of the string after "_".

 

[Junarkar]

Your aim and code do not align. Your code should be:

Range("K2").Value = Left(ActiveSheet.Name, InStr(ActiveSheet.Name, "_") - 1)

Syntax you have written

Len(ActiveSheet.Name) - InStr(ActiveSheet.Name, "_")

is more useful to take remainder part of the string after "_".

Thanks alot shrivallabha...

 

[Junarkar]

Hello Junarkar,

Try it as follows:-

Sub Test()

Dim str As String: str = ActiveSheet.Name

Range("K2").Value = Split(str, "_")(0)

End Sub

I hope that this helps.

Cheerio,
vcoolio.

Thanks vcoolio....

 

[vcoolio]

You're welcome Junarkar.

Cheerio,
vcoolio.