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

Mulb90

New Member
Joined
Mar 31, 2022
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
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?
 

Attachments

  • Capture.PNG
    Capture.PNG
    21.1 KB · Views: 13

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,587
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 31, 2022
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
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
BCDEFGHIJKLMNOPQRSTUV
3
4Original Data Sorted into Columns, only want the last four numbersDesired Result would look like this
5? ? ? 118 70 40 ? 785 6 ? 863 7 ? ? ? ? ?118704078568637118704078568637
6? ? ? ? 60 2.5 ? 66 3 ? ? ? ? ? ? ? ?602.5663602.5663
7? ? ? 60 40 ? 78 2 ? 86 2 ? ? ? ? ? ?60407828626040782862
8? ? ? 50 50 ? 85 2 ? 94 2 ? ? ? ? ? ?50508529425050852942
9? ? ? ? 89 100 87 ? 894 9 ? 983 11 ? ? ? ?89100878949983118910087894998311
10? ? ? ? ? 154 100 87 ? ? 1295 12 ? 1424 14 ? ?1541008712951214241415410087129512142414
11? ? ? ? ? 184 100 87 ? ? 1415 14 ? 1556 17 ? ?1841008714151415561718410087141514155617
12? ? ? ? ? 214 100 87 ? ? 1499 15 ? 1649 18 ? ?2141008714991516491821410087149915164918
13? ? ? ? 61 2.5 ? 67 3 ? ? ? ? ? ? ? ?612.5673612.5673
14? ? 50 50 ? 84 2 ? 92 2 ? ? ? ? ? ? ?50508429225050842922
15? ? 84 95 82 ? 1024 8 ? 1127 10 ? ? ? ? ? ?8495821024811271084958210248112710
16? ? ? 174 105 86 ? ? 1611 15 ? 1772 18 ? ? ? ?1741058616111517721817410586161115177218
17? ? ? 194 105 86 ? ? 1731 15 ? 1905 18 ? ? ? ?1941058617311519051819410586173115190518
18? ? ? 214 105 86 ? ? 1806 16 ? 1987 19 ? ? ? ?2141058618061619871921410586180616198719
19? ? ? 234 105 86 ? ? 1916 16 ? 2107 19 ? ? ? ?2341058619161621071923410586191616210719
20? ? ? ? ? 2 234 105 86 ? ? 2274 18 ? 2501 22 ?223410586227418250122223410586227418250122
21? ? ? 254 105 86 ? ? 1977 18 ? 2174 22 ? ? ? ?2541058619771821742225410586197718217422
22? ? ? ? ? 2 254 105 86 ? ? 2482 19 ? 2731 23 ?225410586248219273123225410586248219273123
23? ? ? 107 160 86 ? ? 1396 11 ? 1536 13 ? ? ? ?1071608613961115361310716086139611153613
24? ? ? 107 160 86 ? ? 1396 11 ? 1536 13 ? ? ? ?1071608613961115361310716086139611153613
25? ? 105 105 86 ? 1437 10 ? 1581 12 ? ? ? ? ? ?1051058614371015811210510586143710158112
26? ? ? 236 105 86 ? ? 1961 15 ? 2158 18 ? ? ? ?2361058619611521581823610586196115215818
27? ? ? 236 105 86 ? ? 1961 15 ? 2158 18 ? ? ? ?
28
Sheet1
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,587
Office Version
  1. 365
Platform
  1. Windows
Thanks for the sample data.
Try this with a copy of your data.

VBA Code:
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
BCDEFGH
3
4Original Data
5? ? ? 118 70 40 ? 785 6 ? 863 7 ? ? ? ? ?78568637
6? ? ? ? 60 2.5 ? 66 3 ? ? ? ? ? ? ? ?602.5663
7? ? ? 60 40 ? 78 2 ? 86 2 ? ? ? ? ? ?782862
8? ? ? 50 50 ? 85 2 ? 94 2 ? ? ? ? ? ?852942
9? ? ? ? 89 100 87 ? 894 9 ? 983 11 ? ? ? ?894998311
10? ? ? ? ? 154 100 87 ? ? 1295 12 ? 1424 14 ? ?129512142414
11? ? ? ? ? 184 100 87 ? ? 1415 14 ? 1556 17 ? ?141514155617
12? ? ? ? ? 214 100 87 ? ? 1499 15 ? 1649 18 ? ?149915164918
13? ? ? ? 61 2.5 ? 67 3 ? ? ? ? ? ? ? ?612.5673
14? ? 50 50 ? 84 2 ? 92 2 ? ? ? ? ? ? ?842922
15? ? 84 95 82 ? 1024 8 ? 1127 10 ? ? ? ? ? ?10248112710
16? ? ? 174 105 86 ? ? 1611 15 ? 1772 18 ? ? ? ?161115177218
17? ? ? 194 105 86 ? ? 1731 15 ? 1905 18 ? ? ? ?173115190518
18? ? ? 214 105 86 ? ? 1806 16 ? 1987 19 ? ? ? ?180616198719
19? ? ? 234 105 86 ? ? 1916 16 ? 2107 19 ? ? ? ?191616210719
20? ? ? ? ? 2 234 105 86 ? ? 2274 18 ? 2501 22 ?227418250122
21? ? ? 254 105 86 ? ? 1977 18 ? 2174 22 ? ? ? ?197718217422
22? ? ? ? ? 2 254 105 86 ? ? 2482 19 ? 2731 23 ?248219273123
23? ? ? 107 160 86 ? ? 1396 11 ? 1536 13 ? ? ? ?139611153613
24? ? ? 107 160 86 ? ? 1396 11 ? 1536 13 ? ? ? ?139611153613
25? ? 105 105 86 ? 1437 10 ? 1581 12 ? ? ? ? ? ?143710158112
26? ? ? 236 105 86 ? ? 1961 15 ? 2158 18 ? ? ? ?196115215818
27? ? ? 236 105 86 ? ? 1961 15 ? 2158 18 ? ? ? ?196115215818
Sheet1
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
Hi,

A formula option:

Book3.xlsx
BCDEFG
3
4Original Data
5? ? ? 118 70 40 ? 785 6 ? 863 7 ? ? ? ? ?78568637
6? ? ? ? 60 2.5 ? 66 3 ? ? ? ? ? ? ? ?602.5663
7? ? ? 60 40 ? 78 2 ? 86 2 ? ? ? ? ? ?782862
8? ? ? 50 50 ? 85 2 ? 94 2 ? ? ? ? ? ?852942
9? ? ? ? 89 100 87 ? 894 9 ? 983 11 ? ? ? ?894998311
10? ? ? ? ? 154 100 87 ? ? 1295 12 ? 1424 14 ? ?129512142414
11? ? ? ? ? 184 100 87 ? ? 1415 14 ? 1556 17 ? ?141514155617
12? ? ? ? ? 214 100 87 ? ? 1499 15 ? 1649 18 ? ?149915164918
13? ? ? ? 61 2.5 ? 67 3 ? ? ? ? ? ? ? ?612.5673
14? ? 50 50 ? 84 2 ? 92 2 ? ? ? ? ? ? ?842922
15? ? 84 95 82 ? 1024 8 ? 1127 10 ? ? ? ? ? ?10248112710
16? ? ? 174 105 86 ? ? 1611 15 ? 1772 18 ? ? ? ?161115177218
17? ? ? 194 105 86 ? ? 1731 15 ? 1905 18 ? ? ? ?173115190518
18? ? ? 214 105 86 ? ? 1806 16 ? 1987 19 ? ? ? ?180616198719
19? ? ? 234 105 86 ? ? 1916 16 ? 2107 19 ? ? ? ?191616210719
20? ? ? ? ? 2 234 105 86 ? ? 2274 18 ? 2501 22 ?227418250122
21? ? ? 254 105 86 ? ? 1977 18 ? 2174 22 ? ? ? ?197718217422
22? ? ? ? ? 2 254 105 86 ? ? 2482 19 ? 2731 23 ?248219273123
23? ? ? 107 160 86 ? ? 1396 11 ? 1536 13 ? ? ? ?139611153613
24? ? ? 107 160 86 ? ? 1396 11 ? 1536 13 ? ? ? ?139611153613
25? ? 105 105 86 ? 1437 10 ? 1581 12 ? ? ? ? ? ?143710158112
26? ? ? 236 105 86 ? ? 1961 15 ? 2158 18 ? ? ? ?196115215818
Sheet1065
Cell Formulas
RangeFormula
D5:G26D5=TRIM(MID(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE($C5,"?"," "))," ",REPT(" ",100)),400),COLUMNS($D5:D5)*100-99,100))
 
Solution

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
1,057
Office Version
  1. 365
Platform
  1. Windows
Or

VBA Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,587
Office Version
  1. 365
Platform
  1. Windows
Another option without RegExp but still with fairly minimal code/worksheet interaction and using the Text to Columns method mentioned:

VBA Code:
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

New Member
Joined
Mar 31, 2022
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
You're welcome, thanks for the feedback, glad we can help.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
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:

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

Original Formula for Text results, leading 0s preserved:

Excel Formula:
=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.
 

Forum statistics

Threads
1,176,265
Messages
5,902,239
Members
434,953
Latest member
matthiasarnbert

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top