Remove Everything When Numbers End To Adjacent Column

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,748
Office Version
  1. 365
Platform
  1. Windows
I have numbers as below. When the numbers end I want everything removed to the adjacent column. I cant use text to columns as there are varying lengths etc. So a code or formula please.

P.S Some may not have letters etc after numbers for example WA2345

Before

Excel 2010
Row\Col
AE
15998
WA11711N
15999
WA20293R
16000
WA22721N-WSD
16001
WA20563-OS
16002
WA20700N
16003
WA20562-OS
Sheet: Sheet1

After

Excel 2010
Row\Col
AE
AF
15997
WA20325N
15998
WA11711N
15999
WA20293R
16000
WA22721N-WSD
16001
WA20563-OS
16002
WA20700N
16003
WA20562-OS
Sheet: Sheet1
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Hi
see this tester code
If this what you need, you may amend or tell me
Code:
Sub tester()
 Dim sm As Object, a
 a = Application.Transpose(Cells(23, 1).Resize(6))
 ReDim b(1 To 6, 1 To 2)
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "(.+?\d+)|(.?|w)+"
        For j = 1 To 6
        Set m = .Execute(a(j))
        For i = 0 To m.Count - 1
            Set sm1 = m(0).submatches
            Set sm2 = m(1)
                b(j, 1) = sm1(0)
                b(j, 2) = sm2
        Next
        Next
    End With
    [b23].Resize(UBound(b, 1), 2) = b
End Sub
In here the data in range A23 down
 
Upvote 0
Non regex method

Place in (standard) module
Code:
Function RemoveEnd(cVal As String)
    On Error Resume Next
    Dim c As Long, x As String
    x = cVal
    For c = Len(x) To 1 Step -1
        Select Case Mid(x, c, 1)
            Case 0 To 9:    Exit For
            Case Else:      x = Left(x, c - 1)
        End Select
    Next c
    If x = "" Then x = cVal
    RemoveEnd = x
End Function

Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
1
OiginalResult1Result2in B2 copied downin C2 copied down
2
WA11711NWA11711N =RemoveEnd(A2) =SUBSTITUTE(A2,B2,"")
3
WA20293RWA20293R
4
WA22721N-WSDWA22721N-WSD
5
WA20563-OSWA20563-OS
6
WA20700NWA20700N
7
WA20562-OSWA20562-OS
8
ABCDEFABCDEF
9
Sheet: Sheet4
 
Upvote 0
Non regex method

Place in (standard) module
Code:
Function RemoveEnd(cVal As String)
    On Error Resume Next
    Dim c As Long, x As String
    x = cVal
    For c = Len(x) To 1 Step -1
        Select Case Mid(x, c, 1)
            Case 0 To 9:    Exit For
            Case Else:      x = Left(x, c - 1)
        End Select
    Next c
    If x = "" Then x = cVal
    RemoveEnd = x
End Function

Works good thanks
 
Last edited:
Upvote 0
Sorry just noticed there are some numbers like this WA13381N-6G so the result is WA13381N-6. Where like the others it should be WA13381 and N-6G
 
Last edited:
Upvote 0
Suggested alternative rule
- first truncate at LAST found hyphen (if any)
- then truncate at LAST found number


perhaps ...

Code:
Function RemoveEnd(cVal As String)
    On Error Resume Next
    Dim c As Long, x As String
    x = cVal
    c = InStr(x, "-")
    If c > 0 Then x = Left(x, c - 1)
    For c = Len(x) To 1 Step -1
        Select Case Mid(x, c, 1)
            Case 0 To 9:    Exit For
            Case Else:      x = Left(x, c - 1)
        End Select
    Next c
    If x = "" Then x = cVal
    RemoveEnd = x
End Function


Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
1
OiginalResult1Result2in B2 copied downin C2 copied down
2
WA11711NWA11711N =RemoveEnd(A2) =SUBSTITUTE(A2,B2,"")
3
WA20293RWA20293R
4
WA22721N-WSDWA22721N-WSD
5
WA20563-OSWA20563-OS
6
WA20700NWA20700N
7
WA20562-OSWA20562-OS
8
ABCDEF1ABCDEF1
9
WA13381N-6GWA13381N-6G
10
Sheet: Sheet4
 
Last edited:
Upvote 0
Maybe try this formula solution

1] In B2, copied down :

=IFERROR(LEFT(A2,MATCH(1,INDEX(-MID(LEFT(A2,FIND("-",A2&"-")),ROW($1:$99),1),0))),A2)

2] In C2, copied down :

=SUBSTITUTE(A2,B2,"")

Regards
Bosco
 
Upvote 0
Are you over-complicating :confused: :confused:

EVERY example provided by you could be returned with

=LEFT(A2,7)

Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
H
1
OiginalResult1Result2in B2 copied downin C2 copied downSAME as BIn F2 copied down
2
WA11711NWA11711N =RemoveEnd(A2) =SUBSTITUTE(A2,B2,"")WA11711 =LEFT(A2,7)
3
WA20293RWA20293RWA20293
4
WA22721N-WSDWA22721N-WSDWA22721
5
WA20563-OSWA20563-OSWA20563
6
WA20700NWA20700NWA20700
7
WA20562-OSWA20562-OSWA20562
8
WA13381N-6GWA13381N-6GWA13381
9
Sheet: Sheet4
 
Upvote 0
Suggested alternative rule
- first truncate at LAST found hyphen (if any)
- then truncate at LAST found number


perhaps ...

Code:
Function RemoveEnd(cVal As String)
    On Error Resume Next
    Dim c As Long, x As String
    x = cVal
    c = InStr(x, "-")
    If c > 0 Then x = Left(x, c - 1)
    For c = Len(x) To 1 Step -1
        Select Case Mid(x, c, 1)
            Case 0 To 9:    Exit For
            Case Else:      x = Left(x, c - 1)
        End Select
    Next c
    If x = "" Then x = cVal
    RemoveEnd = x
End Function
Nice one thanks
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

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
Back
Top