extract text before, between, and after commas in text string

[matthew101]

I would greatly appreciate if anyone can help with a formula to extract the text before, between and after commas in the following:
5,6,31,3,1 or 14,3,24,2,14 there will be a max of 3 commas and 2 numbers separated by commas, so minimum of 9 characters and max of 14.

 

[tlowry]

Welcome to Mr. Excel

Formula:

=SUBSTITUTE(A1,","," ")

UDF:

Function MakeCommasGo(sIn) As String
    MakeCommasGo = Replace(sIn, ",", " ")

End Function

 

[jtakw]

I would greatly appreciate if anyone can help with a formula to extract the text before, between and after commas in the following:
5,6,31,3,1 or 14,3,24,2,14 there will be a max of 3 commas and 2 numbers separated by commas, so minimum of 9 characters and max of 14.

Hi,

You didn't say How you want the numbers Extracted.
Also, your description does Not match your Samples as indicated by red above.

Do you want them in individual cells? If so, B1 formula copied down and across, results converted to Real Numbers for further comparison/math, let me know if you Don't want it converted (will remain as Text):

Book1
	A	B	C	D	E	F
1	5,6,31,3,1	5	6	31	3	1
2	14,3,24,2,14	14	3	24	2	14
Sheet96

Cell Formulas
Range	Formula
B1	=IFERROR(MID(SUBSTITUTE($A1,",",REPT(" ",50)),COLUMNS($B1:B1)*50-49,50)+0,"")

 

[matthew101]

Hi,

You didn't say How you want the numbers Extracted.
Also, your description does Not match your Samples as indicated by red above.

Do you want them in individual cells? If so, B1 formula copied down and across, results converted to Real Numbers for further comparison/math, let me know if you Don't want it converted (will remain as Text):

	A	B	C	D	E	F

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]5,6,31,3,1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]14,3,24,2,14[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]14[/TD]

</tbody>

Sheet96​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=IFERROR(MID(SUBSTITUTE($A1,",",REPT(" ",50)),COLUMNS($B1:B1)*50-49,50)+0,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hi jtakw,
Thank you for your response.
I tried this solution and I am getting #NAME ? error. The cell containing the numbers separated by commas are ages. I would like to extract each age from the cell. Ages can be extracted to separate columns are fine or another method if more simple.
Thank you.
Matt

 

[MARK858]

Do you happen to be using an old version of Excel?

 

[matthew101]

Version 16.14.1 on macOS

 

[matthew101]

It works! Thank you jtakw

 

[jtakw]

You're welcome, glad it worked for you.