Extract number from the end of text string in Excel - Stack Overflow

admin管理员组

文章数量:1026989

I am trying to extract the number from the string like this,

Raw Data
XY1
OPO13
RAW_U9

I am trying to extract the number from the string like this,

Raw Data
XY1
OPO13
RAW_U9

I expect only to get the number from the end of the text string,

Output
1
13
9

I did some research and it told me how to extract the number which is separated by the space, "-" or the "/". But it is hard to extract the number from a string which is not in a fixed pattern.

I read a tutorial using this formula for this purpose:

RIGHT(cell, LEN(cell) - MAX(IF(ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(cell))), 0)))

However, the result is not as it is shown. I got this result instead:

Wrong output when I use this formula
Y1
PO13
AW_U9

Is it something I did wrong? Or should I use another method to get the number from the end of the string?

• excel
• excel-formula

Share Improve this question Follow asked Nov 16, 2024 at 14:00 LightLight 4766 bronze badges 6

• 1 @Blackcat Thank you! I found the reason for the wrong output when I applied the formula. Since it is an array formula and I should use Ctrl + Shift + Enter instead of just pressing Enter to exit editing mode. I am so silly to make this mistake and spent a lot of time looking for the answer. Should I just delete this question? – Light Commented Nov 16, 2024 at 14:43
• 1 Here is an answer for your issue meta.stackoverflow/questions/332101/… – Black cat Commented Nov 16, 2024 at 15:18
• This may come in handy: =MAX(IFERROR(--RIGHT(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))),)) or =MAX(IFERROR(--RIGHT(A1,SEQUENCE(LEN(A1))),)) – P.b Commented Nov 16, 2024 at 15:57
• 1 @Blackcat has used proper link to mark it as duplicate, however, one can use this as well, if they dont want to commit CSE =AGGREGATE(14,7,--RIGHT(A1,ROW($ZZ$1:INDEX($Z:$Z,LEN(A1)))),1) also requested to research the forum, such problems/queries are resolved multiple times. – Mayukh Bhattacharya Commented Nov 16, 2024 at 16:40
• 1 One important caveat, you should remember, don't use volatile functions like INDIRECT() its called the Excels Evil Function. Since its Single Threaded, its Very Slow , Error Prone and Fragile and will always recalculate whenever there is a change in any open workbook. It is highly recommended and suggested to avoid. One more thing, dont not use leading zeroes (except for the number zero itself that has the decimal representation 0 consisting of a single digit symbol that is both leading & ending). Hence the number of fingers on your hands combined is written as 10, not 010 or 0010 – Mayukh Bhattacharya Commented Nov 18, 2024 at 3:51

 |  Show 1 more comment

5 Answers 5

Sorted by: Reset to default 3

Using the logic in OP with Name Manager for ease of (re)use:

• for entries in column A, while in the first row define

Name	Refers to:
cell	=Sheet1!$A1
char_indices	=ROW(INDIRECT("1:" & LEN(cell)))
chars	=MID(cell, char_indices, 1)
right_digits	=RIGHT(cell, LEN(cell) - MAX(NOT(ISNUMBER(--chars)) * char_indices))
right_num	=IFERROR(--right_digits, "")

Then use =right_digits or =right_num

Assuming there is no Excel Constraints as per the post tags then one of the following formula should work for you in MS365

---

• Formula used in cell B2

=MAP(A2:A6,LAMBDA(α,-LOOKUP(1,-RIGHT(α,SEQUENCE(99)))))

---

• Or, without spill version, needs to copy down:

=-LOOKUP(1,-RIGHT(A2,SEQUENCE(99)))

---

• Or, without the use of SEQUENCE() function, if you are not using MS365 or Excel 2021 or Excel 2024 then:

=-LOOKUP(1,-RIGHT(A2,ROW($1:$99)))

---

• Or, using AGGREGATE() function which I have commented above in the thread, before the post was reopened by one of the users :

=AGGREGATE(14,7,--RIGHT(A2,ROW($ZZ$1:INDEX($Z:$Z,LEN(A2)))),1)

---

_{NOTE: All the above formulas consider the numbers are always at the end of the string as shown in the OP. Also, could not test in older versions for the formulas those support, probably it doesn't need the commitment of CTRL+SHIFT+ENTER while exiting the edit mode.}

Black cat's proposal for Formula to extract numbers from a text string is also available there if you want to extract numbers from anywhere in a string to output a combined value.

If you have the SEQUENCE function, and the numbers are always at the end of the string as you show, you can use the shorter:

=MID(A1,MIN(FIND(SEQUENCE(10,,0),A1&SEQUENCE(10,,0))),255). 

If you do not have the SEQUENCE function, you can use the equivalent:

=MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&"0123456789")),255)

(I'm not sure if you need to use Ctrl+Shift+Enter or just Enter to confirm it)

If the digits might be anywhere in the cell, and you want to extract all the digits, see my answer to this question

If you hope to extract "00129"(leading 0s included) from "AXB1234_CBS00129",

=MID(str,XMATCH(TRUE,ISERROR(--MID(str,SEQUENCE(LEN(str)),1)),,-1)+1,999)

enter image description here

Formula in B2:

=--REGEXEXTRACT(A2:A4,"\d+$")

I am trying to extract the number from the string like this,

Raw Data
XY1
OPO13
RAW_U9

I am trying to extract the number from the string like this,

Raw Data
XY1
OPO13
RAW_U9

I expect only to get the number from the end of the text string,

Output
1
13
9

I did some research and it told me how to extract the number which is separated by the space, "-" or the "/". But it is hard to extract the number from a string which is not in a fixed pattern.

I read a tutorial using this formula for this purpose:

RIGHT(cell, LEN(cell) - MAX(IF(ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(cell))), 0)))

However, the result is not as it is shown. I got this result instead:

Wrong output when I use this formula
Y1
PO13
AW_U9

Is it something I did wrong? Or should I use another method to get the number from the end of the string?

• excel
• excel-formula

Share Improve this question Follow asked Nov 16, 2024 at 14:00 LightLight 4766 bronze badges 6

• 1 @Blackcat Thank you! I found the reason for the wrong output when I applied the formula. Since it is an array formula and I should use Ctrl + Shift + Enter instead of just pressing Enter to exit editing mode. I am so silly to make this mistake and spent a lot of time looking for the answer. Should I just delete this question? – Light Commented Nov 16, 2024 at 14:43
• 1 Here is an answer for your issue meta.stackoverflow/questions/332101/… – Black cat Commented Nov 16, 2024 at 15:18
• This may come in handy: =MAX(IFERROR(--RIGHT(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))),)) or =MAX(IFERROR(--RIGHT(A1,SEQUENCE(LEN(A1))),)) – P.b Commented Nov 16, 2024 at 15:57
• 1 @Blackcat has used proper link to mark it as duplicate, however, one can use this as well, if they dont want to commit CSE =AGGREGATE(14,7,--RIGHT(A1,ROW($ZZ$1:INDEX($Z:$Z,LEN(A1)))),1) also requested to research the forum, such problems/queries are resolved multiple times. – Mayukh Bhattacharya Commented Nov 16, 2024 at 16:40
• 1 One important caveat, you should remember, don't use volatile functions like INDIRECT() its called the Excels Evil Function. Since its Single Threaded, its Very Slow , Error Prone and Fragile and will always recalculate whenever there is a change in any open workbook. It is highly recommended and suggested to avoid. One more thing, dont not use leading zeroes (except for the number zero itself that has the decimal representation 0 consisting of a single digit symbol that is both leading & ending). Hence the number of fingers on your hands combined is written as 10, not 010 or 0010 – Mayukh Bhattacharya Commented Nov 18, 2024 at 3:51

 |  Show 1 more comment

5 Answers 5

Sorted by: Reset to default 3

Using the logic in OP with Name Manager for ease of (re)use:

• for entries in column A, while in the first row define

Name	Refers to:
cell	=Sheet1!$A1
char_indices	=ROW(INDIRECT("1:" & LEN(cell)))
chars	=MID(cell, char_indices, 1)
right_digits	=RIGHT(cell, LEN(cell) - MAX(NOT(ISNUMBER(--chars)) * char_indices))
right_num	=IFERROR(--right_digits, "")

Then use =right_digits or =right_num

Assuming there is no Excel Constraints as per the post tags then one of the following formula should work for you in MS365

---

• Formula used in cell B2

=MAP(A2:A6,LAMBDA(α,-LOOKUP(1,-RIGHT(α,SEQUENCE(99)))))

---

• Or, without spill version, needs to copy down:

=-LOOKUP(1,-RIGHT(A2,SEQUENCE(99)))

---

• Or, without the use of SEQUENCE() function, if you are not using MS365 or Excel 2021 or Excel 2024 then:

=-LOOKUP(1,-RIGHT(A2,ROW($1:$99)))

---

• Or, using AGGREGATE() function which I have commented above in the thread, before the post was reopened by one of the users :

=AGGREGATE(14,7,--RIGHT(A2,ROW($ZZ$1:INDEX($Z:$Z,LEN(A2)))),1)

---

_{NOTE: All the above formulas consider the numbers are always at the end of the string as shown in the OP. Also, could not test in older versions for the formulas those support, probably it doesn't need the commitment of CTRL+SHIFT+ENTER while exiting the edit mode.}

Black cat's proposal for Formula to extract numbers from a text string is also available there if you want to extract numbers from anywhere in a string to output a combined value.

If you have the SEQUENCE function, and the numbers are always at the end of the string as you show, you can use the shorter:

=MID(A1,MIN(FIND(SEQUENCE(10,,0),A1&SEQUENCE(10,,0))),255). 

If you do not have the SEQUENCE function, you can use the equivalent:

=MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&"0123456789")),255)

(I'm not sure if you need to use Ctrl+Shift+Enter or just Enter to confirm it)

If the digits might be anywhere in the cell, and you want to extract all the digits, see my answer to this question

If you hope to extract "00129"(leading 0s included) from "AXB1234_CBS00129",

=MID(str,XMATCH(TRUE,ISERROR(--MID(str,SEQUENCE(LEN(str)),1)),,-1)+1,999)

enter image description here

Formula in B2:

=--REGEXEXTRACT(A2:A4,"\d+$")

本文标签： Extract number from the end of text string in ExcelStack Overflow