set variable for string after first underscore

charlesdh · #1 07-09-2018, 11:05 AM

HI,
I'm having a senior moment.

I'm wanting to get text after the first underscore.
My data look s like this:
z_WWWWW_PR
zzz_AAAA_pk

What I'm looking for is:
WWWWW_PR
AAA_pk
I've search for a answer and found something close to what I want but it remove both underscores.

Any help appreciated.

Thanks

ArviLaanemets · #2 07-09-2018, 11:33 AM

The worksheet formula is like (assumed the text is in cell A2, on fly)

Code:

=IFERROR(RIGHT(A2,Find("_",A2)), A2)

Sorry! With Office 2010

Code:

=IF(ISERROR(RIGHT(A2,Find("_",A2)-1)), A2,RIGHT(A2,Find("_",A2)))

When you need this in VBA, then instead FIND() you have to use InStr(), and If ... End If to handle cases when the string is not found (then InStr() returns 0).
Something like:

Code:

If InStr(SourceVariable, "_") = 0 Then
     ResultVariable = SourceVariable
Else
     ResultVariable = Right(SourceVariable, Len(SourceVariable) - InStr(SourceVariable, "_"))
End If

charlesdh · #3 07-09-2018, 11:36 AM

Arv,
Thanks for the reply. I should have mention I wanted to use code.
And have a variable for the result.
Thanks

ArviLaanemets · #4 07-09-2018, 11:54 AM

I updated my 1st post!

charlesdh · #5 07-10-2018, 11:29 AM

Arv,

Thanks for the help. Your code works.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Assigning a string variable to a userform label caption	Larry_1	Excel Programming	3	12-18-2017 06:59 AM
I want to create a bar chart of multiple variable. Then I need to draw trend lin of those variable	shimulsiddiquee	Excel	1	05-16-2017 07:39 AM
Emails to recipients with underscore in email address not received	gjskerritt	Outlook	0	03-14-2017 06:27 PM
Run Time Error '91': Object variable or With block variable not set using Catalogue Mailmerge	Berryblue	Mail Merge	1	11-13-2014 05:36 PM
Why unable to use underscore in microsoft word styles( user defined)?	noufalcm	Word	1	03-08-2011 01:46 PM