Copy last number from comma seperated cell

[Lewiskj]

961, 8370, 9290, 9526, 9608, 10606, 10960, 11763, 13513, 15384, 17232,
2012, 3070, 4387, 4853, 5155, 6191, 8582, 9362, 10606, 10667, 11501, 11546, 11763, 12522, 14415, 15547, 16399,
219, 862, 2855, 4387, 7431, 9292, 9322, 9443, 11010, 11424, 11593, 11763, 13168, 14026, 14207, 14265, 14447, 14723, 15946, 16046, 17066,
919, 943, 2267, 3218, 4723, 6543, 6792, 7431, 7747, 8994, 9019, 9064, 9438, 10671, 11719, 11998, 12545, 12686, 13287, 14942, 16812,
2218, 3631, 6792, 7910, 8557, 8673, 8711, 10744, 11181, 15685, 16313, 17168, 17202,
601, 919, 1429, 2037, 3631, 5531, 7431, 8220, 8986, 9064, 9438, 9759, 10606, 12545, 14957, 15372, 15384, 16346,
599, 4403, 4920, 5155, 6068, 6751, 7633, 9322, 9435, 10182, 10667, 14265, 14354, 14723, 14957, 15806,
746, 1387, 2788, 3253, 4086, 6769, 6792, 6963, 8370, 8555, 8922, 10843, 10960, 13397, 13721, 14577, 14942, 17246,
206, 1387, 1447, 1566, 2267, 3070, 4135, 4387, 4475, 5155, 5457, 5791, 6769, 7542, 8673, 9211, 9526, 9661, 10945, 11491, 11538, 12750, 12839, 13681, 13993, 15884, 16399,
961, 3617, 4920, 10866, 10985, 13397, 13447, 16349, 17670,
219, 2012, 3165, 3253, 3590, 4230, 6029, 8107, 8727, 8994, 10078, 10182, 10235, 11593, 11795, 14957, 16208,
601, 2163, 2342, 6751, 7115, 7852, 8100, 9064, 11607, 12085, 13436, 13513, 13721, 14075, 14577, 16313, 17282,
746, 919, 2012, 2123, 2163, 2855, 4230, 4853, 7104, 8582, 8723, 9362, 9435, 10078, 10235, 10667, 10985, 10987, 11719, 12987, 13876, 14622, 15367, 15884, 16812, 17446, 17670,

I have a long list of comma seperated numbers in column "J"
I wound like to copy the last number in each of these cells to the next cell to the right "K"
i.e I would like K1 to show 17232 K2 to show 16399

Is this possible with a formula or even VBA?

P.S each cell ends with a comma then a space after the last number

 

[Rick Rothstein]

Assuming your text in any one cell will never be larger than 400 characters total...

=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",400)),400)),",","")

 

[Lewiskj]

Assuming your text in any one cell will never be larger than 400 characters total...

=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",400)),400)),",","")

The column I have the data in is "J" so I altered it to
=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(J1," ",REPT(" ",400)),400)),",","")
and put this formula in "K1"

But nothing shows in "K1"

 

[Lewiskj]

The column I have the data in is "J" so I altered it to
=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(J1," ",REPT(" ",400)),400)),",","")
and put this formula in "K1"

But nothing shows in "K1"

I just deleted the comma and space from the end of the cell with data and it worked.
Can this be done within the formula?
I also just deleted the space at the end after the last comma and this works to

 

[Peter_SSs]

Assuming your text in any one cell will never be larger than 400 characters total...

Why that restriction?

I just deleted the comma and space from the end of the cell

Your sample data above does not show or copy with a space at the end. for the future, consider XL2BB for providing sample data.

You could try this

22 07 06.xlsm
	J	K
1	961, 8370, 9290, 9526, 9608, 10606, 10960, 11763, 13513, 15384, 17232,	17232
2	2012, 3070, 4387, 4853, 5155, 6191, 8582, 9362, 10606, 10667, 11501, 11546, 11763, 12522, 14415, 15547, 16399,	16399
3	219, 862, 2855, 4387, 7431, 9292, 9322, 9443, 11010, 11424, 11593, 11763, 13168, 14026, 14207, 14265, 14447, 14723, 15946, 16046, 17066,	17066
4	919, 943, 2267, 3218, 4723, 6543, 6792, 7431, 7747, 8994, 9019, 9064, 9438, 10671, 11719, 11998, 12545, 12686, 13287, 14942, 16812,	16812
5	2218, 3631, 6792, 7910, 8557, 8673, 8711, 10744, 11181, 15685, 16313, 17168, 17202,	17202
6	601, 919, 1429, 2037, 3631, 5531, 7431, 8220, 8986, 9064, 9438, 9759, 10606, 12545, 14957, 15372, 15384, 16346,	16346
7	599, 4403, 4920, 5155, 6068, 6751, 7633, 9322, 9435, 10182, 10667, 14265, 14354, 14723, 14957, 15806,	15806
8	746, 1387, 2788, 3253, 4086, 6769, 6792, 6963, 8370, 8555, 8922, 10843, 10960, 13397, 13721, 14577, 14942, 17246,	17246
9	206, 1387, 1447, 1566, 2267, 3070, 4135, 4387, 4475, 5155, 5457, 5791, 6769, 7542, 8673, 9211, 9526, 9661, 10945, 11491, 11538, 12750, 12839, 13681, 13993, 15884, 16399,	16399
10	961, 3617, 4920, 10866, 10985, 13397, 13447, 16349, 17670,	17670
11	219, 2012, 3165, 3253, 3590, 4230, 6029, 8107, 8727, 8994, 10078, 10182, 10235, 11593, 11795, 14957, 16208,	16208
12	601, 2163, 2342, 6751, 7115, 7852, 8100, 9064, 11607, 12085, 13436, 13513, 13721, 14075, 14577, 16313, 17282,	17282
13	746, 919, 2012, 2123, 2163, 2855, 4230, 4853, 7104, 8582, 8723, 9362, 9435, 10078, 10235, 10667, 10985, 10987, 11719, 12987, 13876, 14622, 15367, 15884, 16812, 17446, 17670,746, 919, 2012, 2123, 2163, 2855, 4230, 4853, 7104, 8582, 8723, 9362, 9435, 10078, 10235, 10667, 10985, 10987, 11719, 12987, 13876, 14622, 15367, 15884, 16812, 17446, 17670,746, 919, 2012, 2123, 2163, 2855, 4230, 4853, 7104, 8582, 8723, 9362, 9435, 10078, 10235, 10667, 10985, 10987, 11719, 12987, 13876, 14622, 15367, 15884, 16812, 17446, 999,	999
Last Number

Cell Formulas
Range	Formula
K1:K13	K1	=LEFT(RIGHT(SUBSTITUTE(TRIM(J1)," ",REPT(" ",20)),20),19)+0

 

[Lewiskj]

Thaks that works great.
Sorry my fault I will use XL2BB in future

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

BTW, I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For example, if you happen to have the latest text functions in your version of Excel, you could do it even more simply like this.

22 07 06.xlsm
	J	K
1	961, 8370, 9290, 9526, 9608, 10606, 10960, 11763, 13513, 15384, 17232,	17232
2	2012, 3070, 4387, 4853, 5155, 6191, 8582, 9362, 10606, 10667, 11501, 11546, 11763, 12522, 14415, 15547, 16399,	16399
3	219, 862, 2855, 4387, 7431, 9292, 9322, 9443, 11010, 11424, 11593, 11763, 13168, 14026, 14207, 14265, 14447, 14723, 15946, 16046, 17066,	17066
4	919, 943, 2267, 3218, 4723, 6543, 6792, 7431, 7747, 8994, 9019, 9064, 9438, 10671, 11719, 11998, 12545, 12686, 13287, 14942, 16812,	16812
5	2218, 3631, 6792, 7910, 8557, 8673, 8711, 10744, 11181, 15685, 16313, 17168, 17202,	17202
6	601, 919, 1429, 2037, 3631, 5531, 7431, 8220, 8986, 9064, 9438, 9759, 10606, 12545, 14957, 15372, 15384, 16346,	16346
7	599, 4403, 4920, 5155, 6068, 6751, 7633, 9322, 9435, 10182, 10667, 14265, 14354, 14723, 14957, 15806,	15806
8	746, 1387, 2788, 3253, 4086, 6769, 6792, 6963, 8370, 8555, 8922, 10843, 10960, 13397, 13721, 14577, 14942, 17246,	17246
9	206, 1387, 1447, 1566, 2267, 3070, 4135, 4387, 4475, 5155, 5457, 5791, 6769, 7542, 8673, 9211, 9526, 9661, 10945, 11491, 11538, 12750, 12839, 13681, 13993, 15884, 16399,	16399
10	961, 3617, 4920, 10866, 10985, 13397, 13447, 16349, 17670,	17670
11	219, 2012, 3165, 3253, 3590, 4230, 6029, 8107, 8727, 8994, 10078, 10182, 10235, 11593, 11795, 14957, 16208,	16208
12	601, 2163, 2342, 6751, 7115, 7852, 8100, 9064, 11607, 12085, 13436, 13513, 13721, 14075, 14577, 16313, 17282,	17282
13	746, 919, 2012, 2123, 2163, 2855, 4230, 4853, 7104, 8582, 8723, 9362, 9435, 10078, 10235, 10667, 10985, 10987, 11719, 12987, 13876, 14622, 15367, 15884, 16812, 17446, 17670,746, 919, 2012, 2123, 2163, 2855, 4230, 4853, 7104, 8582, 8723, 9362, 9435, 10078, 10235, 10667, 10985, 10987, 11719, 12987, 13876, 14622, 15367, 15884, 16812, 17446, 17670,746, 919, 2012, 2123, 2163, 2855, 4230, 4853, 7104, 8582, 8723, 9362, 9435, 10078, 10235, 10667, 10985, 10987, 11719, 12987, 13876, 14622, 15367, 15884, 16812, 17446, 999,	999
Last Number (2)

Cell Formulas
Range	Formula
K1:K13	K1	=TEXTAFTER(TEXTBEFORE(J1,",",-1)," ",-1)+0