Extract Text after Second Underscore in Excel from right side

0 votes

Need to extract text between first and second "_" from a string in a cell.

Example -

 88/12_PO/SP_SJ_#448491_WHITE_10A_60

Required to extract "10A" from the above string:

=MID(B2, SEARCH("_",B2) + 1, SEARCH("_",B2,SEARCH("_",B2)+1) - SEARCH("_",B2) - 1)
Feb 3, 2023 in Others by Kithuzzz
• 38,010 points
773 views

1 answer to this question.

0 votes

The UDF to use.
The default separator is "_", but you can specify any other separator character
The default piece is the 2nd last (index = -2), but you can ask for any other position

Function extract(word As String, Optional separator As String = "_", Optional ByVal index As Long = -2)
    ' index = 1 gives first word, 2 gives 2nd word...
    ' index -1 gives last word, -2 gives 2nd last word...
    Dim tokens() As String
    tokens = Split(word, separator)
    If index < 0 Then index = UBound(tokens) + index + 1 Else index = index - 1
    If index >= 0 And index <= UBound(tokens) Then extract = tokens(index)
End Function

Just use a formula like =Extract(B2) in Excel

answered Feb 3, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

Filter/Extract a text from excel sheet having similar values

ISNUMBER(SEARCH("Maria,",SUBSTITUTE(A1:A4,"]",",")))  shows TRUE if Maria is found and false if it would ...READ MORE

answered Jan 9, 2023 in Others by narikkadan
• 63,420 points
180 views
0 votes
1 answer

How to extract text before the last "," in excel formula?

Using FILTERXML() • Formula used in cell B1 =FILTERXML("<m><b>"&SUBSTITUTE(A1,", ","</b><b>")&"</b></m>","//b[last()-1]") You can ...READ MORE

answered Jan 20, 2023 in Others by narikkadan
• 63,420 points
270 views
0 votes
1 answer

How to scrape the specific text from kworb and extract it as an excel file?

The best practice to scrape tables is ...READ MORE

answered Feb 18, 2023 in Others by narikkadan
• 63,420 points
370 views
0 votes
1 answer

Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
1,286 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
696 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,420 points
1,409 views
0 votes
1 answer

Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,420 points
1,267 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,420 points
966 views
0 votes
1 answer

Extract unique value from the range in Excel

The portability of spreadsheet functions like UNIQUE() ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,420 points
664 views
0 votes
1 answer

Copy Text from Range in Excel into Word Document

Here are some articles that may help: Control ...READ MORE

answered Nov 6, 2022 in Others by narikkadan
• 63,420 points
380 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP