Extract Number of Unknown Length from Text String

[Joruus02]

28 4 4 0 43207 43188

I created the above string with spaces between number. I figured out how to extract the first and last two numbers but I need to pull out the 3rd and 4th numbers (bold). The "answers" will end up in their own cells so I need two separate equations. One to extract each number by itself with the understanding that they number of characters in the number will vary. Lastly, I need to do it without VB. I feel like it's possible with a MID function, looking at the code, but the functions have to include multiple functions and I can't seem to get it quite right. Any assistance would be greatly appreciated.

Notes: M3 is the string ref cell in this case.

 

[jtakw]

Hi,

This is a "Single" formula solution for your request, copy formula across to extract Every part of the number string separated by a space:

Use N3 formula if you Just want to extract the numbers, results will be TEXT.
Use N4 formula if you want results Converted to Real Numbers for further math.

Book1
	M	N	O	P	Q	R	S
3	28 4 4 0 43207 43188	28	4	4	0	43207	43188
4	28 4 4 0 43207 43188	28	4	4	0	43207	43188
Sheet59

Cell Formulas
Range	Formula
N3	=TRIM(MID(SUBSTITUTE($M3," ",REPT(" ",100)),COLUMNS($N1:N1)*100-99,100))
N4	=MID(SUBSTITUTE($M4," ",REPT(" ",100)),COLUMNS($N1:N1)*100-99,100)+0

 

[Joruus02]

Thanks jtakw, you're a wizard. I felt like their had to be a more elegant way. Both equations work exactly as I'd hoped. I haven't tested variable length inputs yet but looking at the code, I don't see variable length strings being a problem. Thank you so much for your quick reply and very easy to read and thorough guidance.

 

[jtakw]

You're welcome, welcome to the forum.

The formula as is, will accommodate up to 100 characters for each individual subset of numbers within the string.