excelexcel-formula
Get the first and last number occuring in a (text) string in a cell
| 0 | A | B | C |
|---|---|---|---|
| 1 | first number | last number | |
| 2 | Top1-5 | 1 | 5 |
| 3 | Top5-10 | 5 | 10 |
| 4 | Top11-15 | 11 | 15 |
In Column B I want to extract the first number occuring in the strings in Column A.
In Column C I want to extract the last number occurring in the strings in Column A.
I tried to apply solutions from this question but they did not work in my case.
Which formula do I need to get the expected results in Column B and Column C?
Solution
This formula accepts any lengths of the numbers.
B20: =LET(perchar,MID(TEXTBEFORE(A20,"-"),SEQUENCE(1,LEN(TEXTBEFORE(A20,"-"))),1), TEXTJOIN("",TRUE,IF(ISNUMBER(--perchar),perchar,"")))
C20: =TEXTAFTER(A20,"-")
- Scoring values including NA() - Excel 365
- Getting the actual usedrange
- Extract non-contiguous data from multiple sheets while extracting from a list with a loop
- Excel VBA - Skip renaming when cell empty
- Protected Cell warning when returning to original cell after DoubleClick
- how to automatically color the lines for each different word, to make it easier to see the difference
- Java Poi XSSF - create pivot table with multiple expandable columns
- Import Excel to Pandas Dataframe, but preserve order of Day/Month in dates?
- Pass an array to a function from a multidimensional array
- VBA Characters.Delete string over 255 characters
- Conditional Formatting Help - Compare Concat cells and Format Range/Table by row based on matches
- problem with VBA code for vlookup from a few different workbooks
- Partial value font color in red using vba
- VBA to manually position an Excel pivot when AutoSort is blocked
- VBA loop through multi-d array and keep values that meet criteria in one column
- Count the value of the current stock in excel
- Extract time from a date time string in excel
- Easy way to export multiple data.frame to multiple Excel worksheets
- Is it possible for HYPERLINK to spill friendly names?
- Loop inside loop. Stop second loop if condition is met
- Using TEXTJOIN to display table values of numbers within certain criteria
- Add attachement to email with VBA
- How to Save DataFrame to Excel file using filedialog as SaveAs-Dialog in Python?
- Excel MS365: 2-D dynamic array creation problem
- Search the latest saved PDF file in a various folders and copy the latest one to a specific location
- How do I get all the different unique combinations of 3 columns using VBA in Excel?
- Worksheet names in VBE
- Excel macro to identify 0 crossing of the value
- Format Multienumeration Data for Pivot Table
- Using Excel, return 1 for all values >0 WITHOUT using an IF