Number between two commas.

[anwaee2]

I would like to be able to extract the numbers between two commas.
Example:
Extract the number any number between the third and fourth commas as getting the number 657 out of the number 12,657,358,623,100.

Again thanks to all for any help.

 

[Sanjeev1976]

You can try the below :

Book1
	A	B
1	First Comma	3
2	Second Comma	4
3
4	12,657,358,623,100
5
6	Result (Excel 2011)	623
7	Result (Excel 365)	623
Sheet2

Cell Formulas
Range	Formula
B6	B6	=TEXT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(A4,",",REPT(" ",LEN(A4))),(B1)*LEN(A4)+1,(B2*LEN(A4))-((B1)*LEN(A4))))," ", ","),"0")
B7	B7	=TEXTJOIN(",",,DROP(TAKE(TEXTSPLIT(A4,","),,B2),,B1))

Also note that the value between third and fourth commas is 623 and not 657

 

[Fluff]

Another option

=REPLACE(LEFT(A2,FIND("^",SUBSTITUTE(A2,",","^",4))-1),1,FIND("^",SUBSTITUTE(A2,",","^",3)),"")

 

[Dave Patton]

"Extract the number any number between the third and fourth commas as getting the number 657 out of the number 12,657,358,623,100."

The question stated number and the count of commas appears to be from the right.
To secure the requested 657 from the number try the following.
I used Fluff's approach on the text equivalent.

T202503a.xlsm
	A	B	C
1	Nnumbers not text	365	Excel 2011
2	12,657,358,623,100	657	657
3	52,223,334,448,888	223	223
4
5
2c

Cell Formulas
Range	Formula
B2:B3	B2	=--LET(a,TEXTSPLIT(TEXT(""&A2,"###,###,###,###,###"),","),INDEX(a,2))
C2:C3	C2	=REPLACE(LEFT(TEXT(""&A2,"###,###,###,###,###"),FIND("^",SUBSTITUTE(TEXT(""&A2,"###,###,###,###,###"),",","^",2))-1),1,FIND("^",SUBSTITUTE(TEXT(""&A2,"###,###,###,###,###"),",","^",1)),"")

 

[anwaee2]

Thanks to all for the replies, You all were very helpful as usual. Thanks again.
Sorry, after testing it, I found it didn't quite do what I needed. My fault. I should have explained better what I was trying to do. I needed to pick out the thousands, millions or what ever no mater the length of the number such as getting 657 (billions) from 12,657,358,623,100 or 125,889,222,657,363,785,345.

 

[Peter_SSs]

It is unclear whether the original data is actually numerical or text. Could be one of these.

25 03 07.xlsm
	A	B
1	If Number
2	12,657,358,623,100	657
3
4	If Text
5	12,657,358,623,100	657
Extract

Cell Formulas
Range	Formula
B2	B2	=--RIGHT(INT(A2/10^9),3)
B5	B5	=--LEFT(RIGHT(SUBSTITUTE(A5,",",REPT(" ",100)),400),100)

 

[Peter_SSs]

no mater the length of the number

You mention number but give 125,889,222,657,363,785,345 as an example. Excel cannot deal with that as a number so if it is in a cell it must be text, in which case the second of my formulas above would extract the 657.

25 03 07.xlsm
	A	B
7	125,889,222,657,363,785,345	657
Extract

Cell Formulas
Range	Formula
B7	B7	=--LEFT(RIGHT(SUBSTITUTE(A7,",",REPT(" ",100)),400),100)