excelfunctionexcel-formulaexcel-2010trim
How to extract text before special character from string in Excel?
I have below text on column A1:
100-This_Is_My-Test_200
Looking to get output as below in column B1:
100-This_Is_My-Test
so basically it needs to remove text starting from last _ (underscore)
I tried below:
=TRIM(RIGHT(SUBSTITUTE(A1,"_",REPT(" ",LEN(A1))),LEN(A1)))
Output: 200
=TRIM(LEFT(SUBSTITUTE(A1,"_",REPT(" ",LEN(A1))),LEN(A1)))
Output: 100-This
Solution
=LEFT(A1,FIND("}}}",SUBSTITUTE(A1,"_","}}}",LEN(A1)-LEN(SUBSTITUTE(A1,"_",""))))-1)
- Draw vertical line in scatter plot?
- Special character check mark
- How to change Font Color of Dates without changing color of other text or background using VBA for Excel
- Use Console as debug window in VBA
- Calculate median using vba
- How can I join cells with a vertical line?
- How to change font color for specific line (row) of Textbox
- excel countif entire row in a table
- Simplifying If Statements Into Loop
- What is the equivalent of the "Days" function or number of days between 2 dates in excel 2007?
- How to find and replace with regex in excel
- How to format graph to show dates from one or more sheets on the horizontal axis?
- How to embed a SVG into a Cell using Aspose.Cells
- Run-Time Error 91 (object variable or with block variable not set) on VBA Email code "mailItem.Display" after IF statements
- loop through excel raw data file and input them to other excel formula sheet and perform iteration calculation
- Deleting duplicate header rows
- mixed date converted from default mm/dd/yy in excel to dd/mm/yy
- Overwrite Excel 2016 worksheet data with data from CSV file
- Laravel validator and excel files error
- Need help aligning a table to a column based on similar values in one table field
- Index rows and columns unexpected results
- Ascii Art using VBA
- How to obtain the full-path names of files read with WorkbookConnections
- How can export all part of columns to pdf file with VBA code?
- Difficult to run
- Allow User Input in Empty Cells, Prevent Editing or Deletion in Occupied Cells
- Export Charts from Excel as images using Python
- Converting 32-bit xll Excel add-in to 64 bit: LNK1561 error
- Changing the Date Format for GROUPED dates in a PIVOT TABLE
- VBA Filter Column Store Unique Values of Another Column