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,669
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,669
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,669
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,082,048
Messages
5,362,890
Members
400,696
Latest member
Kclynn

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top