Separate last two numbers from text.

G

gg

Well-known Member
Joined
Nov 18, 2003
Messages
560
I need a function that will move only the last two numbers to column B and remove them from column A. Using the below data I need to separate the names vs the scores. However, some names have numbers and I need the number that is part of the name to remain. Therefore, If I could only separate the last two digits from the name I'd be OK. I need the score to move to column B and keep the name in column A.

Two examples are in Bold. Those teams have a number at the end of their name. I need that to remain with the name.

Evansville North44
LC Titans41

NKE843 (NKE8 = Name & 43=score)
So. IN Elite31

Locust Grove38
Hawks 5A39 (Hawks 5A = Name & 39 = score)

Locust Grover
East Elite 5th38

OC Thunder47
Hawks 5A33
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
J

JamesLoft

New Member
Joined
Dec 28, 2016
Messages
28
Hey if you want to seperate two last number from test just use =RIGHT(A1,2)
 
Last edited:
Y

YasserKhalil

Well-known Member
Joined
Jun 24, 2010
Messages
815
May be
Code: 
Sub Test()
    Dim arr     As Variant
    Dim i       As Long
    
    arr = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).Resize(, 3).Value
    
    For i = LBound(arr, 1) To UBound(arr, 1)
        If IsNumeric(Right(arr(i, 1), 2)) Then
            arr(i, 3) = Right(arr(i, 1), 2)
            arr(i, 2) = Left(arr(i, 1), Len(arr(i, 1)) - 2)
        Else
            arr(i, 2) = arr(i, 1)
        End If
    Next i
    
    Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).Resize(, 3).Value = arr
End Sub
 
Last edited:
H

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
gg,

If I understand you correctly, then here is a macro solution for you to consider.

Sample raw data in the active worksheet:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Name</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Score</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Evansville North44</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">LC Titans41</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">NKE843</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">So. IN Elite31</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Locust Grove38</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Hawks 5A39</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Locust Grover</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">East Elite 5th38</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">OC Thunder47</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Hawks 5A33</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

And, after the macro:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Name</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Score</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Evansville North</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">44</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">LC Titans</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">41</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">NKE8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">43</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">So. IN Elite</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">31</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Locust Grove</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">38</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Hawks 5A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">39</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Locust Grover</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">East Elite 5th</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">38</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">OC Thunder</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">47</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Hawks 5A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">33</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Sub SeparateLast2Numbers()
' hiker95, 01/08/2017, ME984393
Dim r As Range
Application.ScreenUpdating = False
With ActiveSheet
  For Each r In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    If IsNumeric(Right(r, 2)) Then
      r.Offset(, 1) = Right(r, 2)
      r = Left(r, Len(r) - 2)
    End If
  Next r
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the SeparateLast2Numbers macro.
 
H

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649

ADVERTISEMENT

gg,

Here is another macro solution for you to consider, that should be faster then my first macro in my reply #4.

With the same screenshots in my reply #4.

With the same instructions as my reply #4.

Code: 
Sub SeparateLast2Numbers_V2()
' hiker95, 01/08/2017, ME984393
Dim a As Variant, i As Long
Application.ScreenUpdating = False
With ActiveSheet
  a = .Range("A2:B" & .Range("A" & Rows.Count).End(xlUp).Row)
  For i = 1 To UBound(a, 1)
    If IsNumeric(Right(a(i, 1), 2)) Then
      a(i, 2) = Right(a(i, 1), 2)
      a(i, 1) = Left(a(i, 1), Len(a(i, 1)) - 2)
    End If
  Next i
  .Range("A2").Resize(UBound(a, 1), UBound(a, 2)) = a
End With
Application.ScreenUpdating = True
End Sub

With the same instructions as my reply #4.

Then run the SeparateLast2Numbers_V2 macro.
 
Last edited:
G

gg

Well-known Member
Joined
Nov 18, 2003
Messages
560
hiker95 said:
gg,

Here is another macro solution for you to consider, that should be faster then my first macro in my reply #4.

With the same screenshots in my reply #4.

With the same instructions as my reply #4.

Code: 
Sub SeparateLast2Numbers_V2()
' hiker95, 01/08/2017, ME984393
Dim a As Variant, i As Long
Application.ScreenUpdating = False
With ActiveSheet
  a = .Range("A2:B" & .Range("A" & Rows.Count).End(xlUp).Row)
  For i = 1 To UBound(a, 1)
    If IsNumeric(Right(a(i, 1), 2)) Then
      a(i, 2) = Right(a(i, 1), 2)
      a(i, 1) = Left(a(i, 1), Len(a(i, 1)) - 2)
    End If
  Next i
  .Range("A2").Resize(UBound(a, 1), UBound(a, 2)) = a
End With
Application.ScreenUpdating = True
End Sub

With the same instructions as my reply #4.

Then run the SeparateLast2Numbers_V2 macro.
Click to expand...

This workd nicely!!
Thank you
 
H

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
gg,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.
 
You must log in or register to reply here.

Similar threads

B
  • Question
Separating phone numbers from text fields, keeping all the data, then moving it around a bit. And breathe.
2
Replies
11
Views
290
KP117
K
U
  • Question
Extracting common numbers from two cells with comma-separated numbers
2
Replies
10
Views
274
Peter_SSs
Peter_SSs
B
  • Question
Separate sections custom sort - numbers with text separate sections
2 3
Replies
26
Views
650
DanteAmor
DanteAmor
A
  • Question
Extracting multiple numbers from one string with blanks & text in it & put into separate columns
2
Replies
13
Views
702
DanteAmor
DanteAmor
U
  • Question
Match Data from two separate excel sheets (same excel workbook).
Replies
1
Views
179
Veritan
V

Watch MrExcel Video

LET Function Second Look: Reusing Variables! 2326

Forum statistics

Threads
1,123,057
Messages
5,599,550
Members
414,316
Latest member
ExcelLee

Share this page

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