Extracting Value in Excel

[jmartin2178]

What would be the appropriate formula to extract the text between the 1st and 2nd underscores?


Text: STR_UAT_19.3_MWaaS: MODIFY_EXISTING_INTERFACE - Business processing area is not populating when MFTP-ID is entered.

* Original Value located in Column: F

 

[njimack]

Will it always be 3 characters?

 

[JoeMo]

=trim(mid(substitute(a1,"_",rept(" ",100)),100,100))

 

[jmartin2178]

Will it always be 3 characters?

No, It could be a various length in characters.

 

[jmartin2178]

=trim(mid(substitute(a1,"_",rept(" ",100)),100,100))

JoeMo for the Win!!!

Appreciate it!

 

[jmartin2178]

=trim(mid(substitute(a1,"_",rept(" ",100)),100,100))

Another quick question, what if I would like to extract the next value highlighted in Red?


Text: STR_UAT_19.3_ MWaaS : MODIFY_EXISTING_INTERFACE - Business processing area is not populating when MFTP-ID is entered.

 

[Fluff]

How about
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,"_",REPT(" ",100),3),":",REPT(" ",100)),100,100))

 

[jmartin2178]

How about
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,"_",REPT(" ",100),3),":",REPT(" ",100)),100,100))

That did it!

 

[Fluff]

You're welcome & thanks for the feedback

 

[kweaver]

I'm (more than) curious about the use of REPT in this task.
Is there good support for this approach versus the following and, if so, why?

With the original text string in A1

Third _ position in A2

=FIND(CHAR(160),SUBSTITUTE(A1,"_",CHAR(160),3))

First : position in A3

=FIND(CHAR(160),SUBSTITUTE(A1,":",CHAR(160),1))

Extraction

MID(A1,A2+1,A3-A2-1)