Remove Everything When Numbers End To Adjacent Column

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,381
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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
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
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,251
Office Version
365
Platform
Windows
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
 

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,381
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:

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,381
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:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,251
Office Version
365
Platform
Windows
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:

bosco_yip

Well-known Member
Joined
Dec 2, 2002
Messages
1,938
Office Version
2019
Platform
Windows
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
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,251
Office Version
365
Platform
Windows
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
 

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,381
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
 

Forum statistics

Threads
1,089,313
Messages
5,407,514
Members
403,150
Latest member
stellalol

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top