Remove Everything When Numbers End To Adjacent Column

Dazzawm

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

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
4,859
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,379
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,379
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
4,859
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
4,859
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,379
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,084,744
Messages
5,379,571
Members
401,613
Latest member
KarimK9

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top