# Separate last two numbers from text.

#### gg

##### Well-known Member
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

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### JamesLoft

##### New Member
Hey if you want to seperate two last number from test just use =RIGHT(A1,2)

Last edited:

#### YasserKhalil

##### Well-known Member
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:

#### hiker95

##### Well-known Member
gg,

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

Sample raw data in the active worksheet:

Excel 2007
AB
1NameScore
2Evansville North44
3LC Titans41
4NKE843
5So. IN Elite31
6Locust Grove38
7Hawks 5A39
8Locust Grover
9East Elite 5th38
10OC Thunder47
11Hawks 5A33
12
Sheet1

And, after the macro:

Excel 2007
AB
1NameScore
2Evansville North44
3LC Titans41
4NKE843
5So. IN Elite31
6Locust Grove38
7Hawks 5A39
8Locust Grover
9East Elite 5th38
10OC Thunder47
11Hawks 5A33
12
Sheet1

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
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.

#### hiker95

##### Well-known Member

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:

#### gg

##### Well-known Member
Hey if you want to seperate two last number from test just use =RIGHT(A1,2)

Any way to take the number from the orginal text in A1?

#### gg

##### Well-known Member
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.

This workd nicely!!
Thank you

#### hiker95

##### Well-known Member
gg,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,010
Messages
5,834,880
Members
430,325
Latest member
Thony

### 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.

### Which adblocker are you using?

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

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