How to sort "Text to Columns" but from the right side?

[Mulb90]

I have a large amount of data that I'm syphoning through and I have a string of numbers that I can sort into sperate columns. I only want the last four numbers of each set and there are multiple blanks.
When doing text to columns it sorts left to right so some lines of data are longer than others due to the blanks. If when doing text to columns IF it can sort it right to left I can easily just copy the last four numbers from each line.
Please refer to image of a sample of the data currently. Is there a way to sort right to left?

 

[Peter_SSs]

Welcome to the MrExcel board!

Could we have the sample data with XL2BB so that we can test without hours of typing?
Also, please provide the expected results for the sample data, also with XL2BB?

 

[Mulb90]

Welcome to the MrExcel board!

Could we have the sample data with XL2BB so that we can test without hours of typing?
Also, please provide the expected results for the sample data, also with XL2BB?

Hi Peter_SSs

Please find below a sample of the data

Book4
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
3
4		Original Data	Sorted into Columns, only want the last four numbers											Desired Result would look like this
5		? ? ? 118 70 40 ? 785 6 ? 863 7 ? ? ? ? ?	118	70	40	785	6	863	7					118	70	40	785	6	863	7
6		? ? ? ? 60 2.5 ? 66 3 ? ? ? ? ? ? ? ?	60	2.5	66	3											60	2.5	66	3
7		? ? ? 60 40 ? 78 2 ? 86 2 ? ? ? ? ? ?	60	40	78	2	86	2							60	40	78	2	86	2
8		? ? ? 50 50 ? 85 2 ? 94 2 ? ? ? ? ? ?	50	50	85	2	94	2							50	50	85	2	94	2
9		? ? ? ? 89 100 87 ? 894 9 ? 983 11 ? ? ? ?	89	100	87	894	9	983	11					89	100	87	894	9	983	11
10		? ? ? ? ? 154 100 87 ? ? 1295 12 ? 1424 14 ? ?	154	100	87	1295	12	1424	14					154	100	87	1295	12	1424	14
11		? ? ? ? ? 184 100 87 ? ? 1415 14 ? 1556 17 ? ?	184	100	87	1415	14	1556	17					184	100	87	1415	14	1556	17
12		? ? ? ? ? 214 100 87 ? ? 1499 15 ? 1649 18 ? ?	214	100	87	1499	15	1649	18					214	100	87	1499	15	1649	18
13		? ? ? ? 61 2.5 ? 67 3 ? ? ? ? ? ? ? ?	61	2.5	67	3											61	2.5	67	3
14		? ? 50 50 ? 84 2 ? 92 2 ? ? ? ? ? ? ?	50	50	84	2	92	2							50	50	84	2	92	2
15		? ? 84 95 82 ? 1024 8 ? 1127 10 ? ? ? ? ? ?	84	95	82	1024	8	1127	10					84	95	82	1024	8	1127	10
16		? ? ? 174 105 86 ? ? 1611 15 ? 1772 18 ? ? ? ?	174	105	86	1611	15	1772	18					174	105	86	1611	15	1772	18
17		? ? ? 194 105 86 ? ? 1731 15 ? 1905 18 ? ? ? ?	194	105	86	1731	15	1905	18					194	105	86	1731	15	1905	18
18		? ? ? 214 105 86 ? ? 1806 16 ? 1987 19 ? ? ? ?	214	105	86	1806	16	1987	19					214	105	86	1806	16	1987	19
19		? ? ? 234 105 86 ? ? 1916 16 ? 2107 19 ? ? ? ?	234	105	86	1916	16	2107	19					234	105	86	1916	16	2107	19
20		? ? ? ? ? 2 234 105 86 ? ? 2274 18 ? 2501 22 ?	2	234	105	86	2274	18	2501	22			2	234	105	86	2274	18	2501	22
21		? ? ? 254 105 86 ? ? 1977 18 ? 2174 22 ? ? ? ?	254	105	86	1977	18	2174	22					254	105	86	1977	18	2174	22
22		? ? ? ? ? 2 254 105 86 ? ? 2482 19 ? 2731 23 ?	2	254	105	86	2482	19	2731	23			2	254	105	86	2482	19	2731	23
23		? ? ? 107 160 86 ? ? 1396 11 ? 1536 13 ? ? ? ?	107	160	86	1396	11	1536	13					107	160	86	1396	11	1536	13
24		? ? ? 107 160 86 ? ? 1396 11 ? 1536 13 ? ? ? ?	107	160	86	1396	11	1536	13					107	160	86	1396	11	1536	13
25		? ? 105 105 86 ? 1437 10 ? 1581 12 ? ? ? ? ? ?	105	105	86	1437	10	1581	12					105	105	86	1437	10	1581	12
26		? ? ? 236 105 86 ? ? 1961 15 ? 2158 18 ? ? ? ?	236	105	86	1961	15	2158	18					236	105	86	1961	15	2158	18
27		? ? ? 236 105 86 ? ? 1961 15 ? 2158 18 ? ? ? ?
28
Sheet1

 

[Peter_SSs]

Thanks for the sample data.
Try this with a copy of your data.

Sub Last_Four()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "(^.*\D)(?=([0-9.]+ ){4}$)"
  With Range("C5", Range("C" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      a(i, 1) = RX.Replace(Application.Trim(Replace(a(i, 1), "?", " ")) & " ", "")
    Next i
    With .Offset(, 1)
      .Value = a
      .TextToColumns DataType:=xlDelimited, Space:=True, Other:=False
    End With
  End With
End Sub

My sample data and results:

Mulb90.xlsm
	B	C	D	E	F	G	H
3
4		Original Data
5		? ? ? 118 70 40 ? 785 6 ? 863 7 ? ? ? ? ?	785	6	863	7
6		? ? ? ? 60 2.5 ? 66 3 ? ? ? ? ? ? ? ?	60	2.5	66	3
7		? ? ? 60 40 ? 78 2 ? 86 2 ? ? ? ? ? ?	78	2	86	2
8		? ? ? 50 50 ? 85 2 ? 94 2 ? ? ? ? ? ?	85	2	94	2
9		? ? ? ? 89 100 87 ? 894 9 ? 983 11 ? ? ? ?	894	9	983	11
10		? ? ? ? ? 154 100 87 ? ? 1295 12 ? 1424 14 ? ?	1295	12	1424	14
11		? ? ? ? ? 184 100 87 ? ? 1415 14 ? 1556 17 ? ?	1415	14	1556	17
12		? ? ? ? ? 214 100 87 ? ? 1499 15 ? 1649 18 ? ?	1499	15	1649	18
13		? ? ? ? 61 2.5 ? 67 3 ? ? ? ? ? ? ? ?	61	2.5	67	3
14		? ? 50 50 ? 84 2 ? 92 2 ? ? ? ? ? ? ?	84	2	92	2
15		? ? 84 95 82 ? 1024 8 ? 1127 10 ? ? ? ? ? ?	1024	8	1127	10
16		? ? ? 174 105 86 ? ? 1611 15 ? 1772 18 ? ? ? ?	1611	15	1772	18
17		? ? ? 194 105 86 ? ? 1731 15 ? 1905 18 ? ? ? ?	1731	15	1905	18
18		? ? ? 214 105 86 ? ? 1806 16 ? 1987 19 ? ? ? ?	1806	16	1987	19
19		? ? ? 234 105 86 ? ? 1916 16 ? 2107 19 ? ? ? ?	1916	16	2107	19
20		? ? ? ? ? 2 234 105 86 ? ? 2274 18 ? 2501 22 ?	2274	18	2501	22
21		? ? ? 254 105 86 ? ? 1977 18 ? 2174 22 ? ? ? ?	1977	18	2174	22
22		? ? ? ? ? 2 254 105 86 ? ? 2482 19 ? 2731 23 ?	2482	19	2731	23
23		? ? ? 107 160 86 ? ? 1396 11 ? 1536 13 ? ? ? ?	1396	11	1536	13
24		? ? ? 107 160 86 ? ? 1396 11 ? 1536 13 ? ? ? ?	1396	11	1536	13
25		? ? 105 105 86 ? 1437 10 ? 1581 12 ? ? ? ? ? ?	1437	10	1581	12
26		? ? ? 236 105 86 ? ? 1961 15 ? 2158 18 ? ? ? ?	1961	15	2158	18
27		? ? ? 236 105 86 ? ? 1961 15 ? 2158 18 ? ? ? ?	1961	15	2158	18
Sheet1

 

[jtakw]

Hi,

A formula option:

Book3.xlsx
	B	C	D	E	F	G
3
4		Original Data
5		? ? ? 118 70 40 ? 785 6 ? 863 7 ? ? ? ? ?	785	6	863	7
6		? ? ? ? 60 2.5 ? 66 3 ? ? ? ? ? ? ? ?	60	2.5	66	3
7		? ? ? 60 40 ? 78 2 ? 86 2 ? ? ? ? ? ?	78	2	86	2
8		? ? ? 50 50 ? 85 2 ? 94 2 ? ? ? ? ? ?	85	2	94	2
9		? ? ? ? 89 100 87 ? 894 9 ? 983 11 ? ? ? ?	894	9	983	11
10		? ? ? ? ? 154 100 87 ? ? 1295 12 ? 1424 14 ? ?	1295	12	1424	14
11		? ? ? ? ? 184 100 87 ? ? 1415 14 ? 1556 17 ? ?	1415	14	1556	17
12		? ? ? ? ? 214 100 87 ? ? 1499 15 ? 1649 18 ? ?	1499	15	1649	18
13		? ? ? ? 61 2.5 ? 67 3 ? ? ? ? ? ? ? ?	61	2.5	67	3
14		? ? 50 50 ? 84 2 ? 92 2 ? ? ? ? ? ? ?	84	2	92	2
15		? ? 84 95 82 ? 1024 8 ? 1127 10 ? ? ? ? ? ?	1024	8	1127	10
16		? ? ? 174 105 86 ? ? 1611 15 ? 1772 18 ? ? ? ?	1611	15	1772	18
17		? ? ? 194 105 86 ? ? 1731 15 ? 1905 18 ? ? ? ?	1731	15	1905	18
18		? ? ? 214 105 86 ? ? 1806 16 ? 1987 19 ? ? ? ?	1806	16	1987	19
19		? ? ? 234 105 86 ? ? 1916 16 ? 2107 19 ? ? ? ?	1916	16	2107	19
20		? ? ? ? ? 2 234 105 86 ? ? 2274 18 ? 2501 22 ?	2274	18	2501	22
21		? ? ? 254 105 86 ? ? 1977 18 ? 2174 22 ? ? ? ?	1977	18	2174	22
22		? ? ? ? ? 2 254 105 86 ? ? 2482 19 ? 2731 23 ?	2482	19	2731	23
23		? ? ? 107 160 86 ? ? 1396 11 ? 1536 13 ? ? ? ?	1396	11	1536	13
24		? ? ? 107 160 86 ? ? 1396 11 ? 1536 13 ? ? ? ?	1396	11	1536	13
25		? ? 105 105 86 ? 1437 10 ? 1581 12 ? ? ? ? ? ?	1437	10	1581	12
26		? ? ? 236 105 86 ? ? 1961 15 ? 2158 18 ? ? ? ?	1961	15	2158	18
Sheet1065

Cell Formulas
Range	Formula
D5:G26	D5	=TRIM(MID(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE($C5,"?"," "))," ",REPT(" ",100)),400),COLUMNS($D5:D5)*100-99,100))

 

[JEC]

Or

Sub jec()
 Dim it, a, x
 For Each it In Range("C3", Range("C" & Rows.Count).End(xlUp))
   a = Filter(Split(it), "?", 0)
   x = Application.Index(a, Evaluate("row(" & UBound(a) - 2 & ":" & UBound(a) + 1 & ")"))
   it.Offset(, 1).Resize(, UBound(x)) = Application.Transpose(x)
 Next
End Sub

 

[Peter_SSs]

Another option without RegExp but still with fairly minimal code/worksheet interaction and using the Text to Columns method mentioned:

Sub Last_Four_v2()
  Dim a As Variant
  Dim i As Long
  
    With Range("C5", Range("C" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      a(i, 1) = StrReverse(Join(Filter(Split(StrReverse(Application.Trim(Replace(a(i, 1), "?", " "))), , 5), " ", False)))
    Next i
    With .Offset(, 1)
      .Value = a
      .TextToColumns DataType:=xlDelimited, Space:=True, Other:=False
    End With
  End With
End Sub

 

[Mulb90]

Thank you everyone for your help, I'm not that confident with VBA yet thankfully jtakw worked out a formula to get the job done

 

[jtakw]

You're welcome, thanks for the feedback, glad we can help.

 

[jtakw]

Just a follow up, as I have no idea what those numbers represent, and don't know if some of them may have leading 0s (e.g. 012, 0056, etc.)
The formula I provided results in Text.
If you Won't have any leading 0s, and want the results converted to Real Numbers, use this slightly modified version:

For Real Numbers results:

=MID(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE($C5,"?"," "))," ",REPT(" ",100)),400),COLUMNS($D5:D5)*100-99,100)+0

Original Formula for Text results, leading 0s preserved:

=TRIM(MID(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE($C5,"?"," "))," ",REPT(" ",100)),400),COLUMNS($D5:D5)*100-99,100))

Apply either formula the same way, formula in D5, copied across and down.