excel-formula
Excel formula to extract alphanumeric string and subtract 1 from the numeric portion
I would like to find a formula that would make the number portion of an alphanumeric string minus one. Is it possible to do it in one formula or must it be done with two separate formulas?
Example:
Original string: P300,
Formula makes it: P299,
Unable to make it work in one formula, can only make it with 2 separate formulas:
=MID(B2,2,3)-1
="P"&G2&","
Note: sometimes it is P that precedes the number and sometimes it is R.
Solution
This makes use of the sequence, textjoin, mid, len, and concatenate function. This will put all of the characters in the text into an array, join the numbers together turning them into a number, and then concatenates a P in front.
- How to fill fast for integer number calculation?
- Combine most recent 5 records into one cell in Excel
- Find Corresponding Value Based on Today's Date Falling Between Week-Ending Dates in Excel
- Date format issue when I try to find the first occurrence of names with associated dates
- Excel - How can we replace multiple characters or whole words in a cell using LAMBDA()
- Nested if statements in EXCEL dependent on first condition
- List only workdays in given dynamic month
- INDIRECT array to refer to a table column not working with array formulas
- Putting a concatenate inside of a count if
- Excel: how to search by two rows and one column
- Excel - Check existing digit count, if <10 then add prefix "100" to make it 10 digit
- Custom Excel formula for length restriction of substring within a string in a cell
- COUNTIFS across multiple columns with multiple criteria
- Occurrence by month using Excel Functions
- Strange behavior in Excel when counting Work Hours and Work Super Hours
- Need help fixing my excel formula so it doesn't show #VALUE! error in end result
- Can I guarantee the Image Formula Function is finished before printing the result?
- Excel filter data in date intervals excluding all 0 values
- Count cells with two conditions
- Formula to filter data based on prior 3 boolean "true" values
- I want the result of a function to return the value at the top of the column in an array instead of the position of the cell it's searching for
- Create a smaller dynamic list in Excel from data in a larger list using multiple criteria
- Sum up values per row based on a flexible array in other column
- Spill formula to sumifs by row & col for only relevant months
- Copy cells based on matching index numbers on excel?
- How to Combine Text Based on Multiple Criteria in Excel
- Xlookup multiple match types
- Separate octets of IP address using formulas
- Dynamic index match using indirect function to pull from other sheets
- Trying to keep last 9 of various strings