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

 

[Fluff]

As we do not know where the data came from I would advise against using Char 160. If the data came from the web it may already contain that character, giving the wrong result.
Using REPT is just one way of keeping the formula in one cell rather than using helper cells.

 

[jtakw]

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.

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

Hi,

Unless the part Text: is Not part of the string, formula provided in Post # 7 did not extract the Red part as requested by OP, perhaps a "small" tweak:

Book1
	A	B	C
2	Text: STR_UAT_19.3_ MWaaS : MODIFY_EXISTING_INTERFACE - Business processing area is not populating when MFTP-ID is entered.	STR_UAT_19.3	MWaaS
3
4		From Post # 7	Fixed
Sheet675

Cell Formulas
Range	Formula
B2	=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,"_",REPT(" ",100),3),":",REPT(" ",100)),100,100))
C2	=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,"_",REPT(" ",100),3),":",REPT(" ",100),2),100,100))

 

[jmartin2178]

Hi,

Unless the part Text: is Not part of the string, formula provided in Post # 7 did not extract the Red part as requested by OP, perhaps a "small" tweak:

	A	B	C
Text: STR_UAT_19.3_ MWaaS : MODIFY_EXISTING_INTERFACE - Business processing area is not populating when MFTP-ID is entered.	STR_UAT_19.3	MWaaS
From Post # 7	Fixed

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet675​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,"_",REPT(" ",100),3),":",REPT(" ",100)),100,100))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,"_",REPT(" ",100),3),":",REPT(" ",100),2),100,100))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Good catch, but in this example the "Text:" was not part of the string. Really appreciate the quick responses and think I found my new favorite message board.

 

[jmartin2178]

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

Thought I could figure this out with the first two examples, but one last question to getting the Red Highlighted value below.

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(A2,"_",REPT(" ",100)),200,100))

If you don't want to convert to a number, remove the -- at the start

 

[jtakw]

Here's a single formula option to extract All 3 parts separately:

Book1
	A	B	C	D
2	STR_UAT_19.3_ MWaaS : MODIFY_EXISTING_INTERFACE - Business processing area is not populating when MFTP-ID is entered.	UAT	19.3	MWaaS
Sheet681

Cell Formulas
Range	Formula
B2	=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A2,":","_"),"_",REPT(" ",100)),COLUMNS($B1:B1)*100,100))

B2 formula copied across to D2.

If you want the values extracted vertically instead, let us know, will tweak formula to accommodate.