Remove Everything When Numbers End To Adjacent Column

Dazzawm

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,783
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
[Table="width:, class:head"][tr=bgcolor:#888888][th]Row\Col[/th][th]
AE​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
15998​
[/td][td=bgcolor:#99CCFF]WA11711N[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
15999​
[/td][td=bgcolor:#99CCFF]WA20293R[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
16000​
[/td][td=bgcolor:#99CCFF]WA22721N-WSD[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
16001​
[/td][td=bgcolor:#99CCFF]WA20563-OS[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
16002​
[/td][td=bgcolor:#99CCFF]WA20700N[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
16003​
[/td][td=bgcolor:#99CCFF]WA20562-OS[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

After

Excel 2010
[Table="width:, class:head"][tr=bgcolor:#888888][th]Row\Col[/th][th]
AE​
[/th][th]
AF​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
15997​
[/td][td=bgcolor:#99CCFF]WA20325[/td][td=bgcolor:#99CCFF]N[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
15998​
[/td][td=bgcolor:#99CCFF]WA11711[/td][td=bgcolor:#99CCFF]N[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
15999​
[/td][td=bgcolor:#99CCFF]WA20293[/td][td=bgcolor:#99CCFF]R[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
16000​
[/td][td=bgcolor:#99CCFF]WA22721[/td][td=bgcolor:#99CCFF]N-WSD[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
16001​
[/td][td=bgcolor:#99CCFF]WA20563[/td][td=bgcolor:#99CCFF]-OS[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
16002​
[/td][td=bgcolor:#99CCFF]WA20700[/td][td=bgcolor:#99CCFF]N[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
16003​
[/td][td=bgcolor:#99CCFF]WA20562[/td][td=bgcolor:#99CCFF]-OS[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]Oiginal[/td][td]Result1[/td][td]Result2[/td][td]in B2 copied down[/td][td]in C2 copied down[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]WA11711N[/td][td]WA11711[/td][td]N[/td][td] =RemoveEnd(A2)[/td][td] =SUBSTITUTE(A2,B2,"")[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]WA20293R[/td][td]WA20293[/td][td]R[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]WA22721N-WSD[/td][td]WA22721[/td][td]N-WSD[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]WA20563-OS[/td][td]WA20563[/td][td]-OS[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]WA20700N[/td][td]WA20700[/td][td]N[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]WA20562-OS[/td][td]WA20562[/td][td]-OS[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]ABCDEF[/td][td]ABCDEF[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet4[/td][/tr][/table]
 
Upvote 0
Yongle said:
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
Click to expand...

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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]Oiginal[/td][td]Result1[/td][td]Result2[/td][td]in B2 copied down[/td][td]in C2 copied down[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]WA11711N[/td][td]WA11711[/td][td]N[/td][td] =RemoveEnd(A2)[/td][td] =SUBSTITUTE(A2,B2,"")[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]WA20293R[/td][td]WA20293[/td][td]R[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]WA22721N-WSD[/td][td]WA22721[/td][td]N-WSD[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]WA20563-OS[/td][td]WA20563[/td][td]-OS[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]WA20700N[/td][td]WA20700[/td][td]N[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]WA20562-OS[/td][td]WA20562[/td][td]-OS[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]ABCDEF1[/td][td]ABCDEF1[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td=bgcolor:#FFFF00]WA13381N-6G[/td][td=bgcolor:#FFFF00]WA13381[/td][td=bgcolor:#FFFF00]N-6G[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet4[/td][/tr][/table]
 
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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]Oiginal[/td][td]Result1[/td][td]Result2[/td][td]in B2 copied down[/td][td]in C2 copied down[/td][td]SAME as B[/td][td]In F2 copied down[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]WA11711N[/td][td]WA11711[/td][td]N[/td][td] =RemoveEnd(A2)[/td][td] =SUBSTITUTE(A2,B2,"")[/td][td]WA11711[/td][td] =LEFT(A2,7)[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]WA20293R[/td][td]WA20293[/td][td]R[/td][td][/td][td][/td][td]WA20293[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]WA22721N-WSD[/td][td]WA22721[/td][td]N-WSD[/td][td][/td][td][/td][td]WA22721[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]WA20563-OS[/td][td]WA20563[/td][td]-OS[/td][td][/td][td][/td][td]WA20563[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]WA20700N[/td][td]WA20700[/td][td]N[/td][td][/td][td][/td][td]WA20700[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]WA20562-OS[/td][td]WA20562[/td][td]-OS[/td][td][/td][td][/td][td]WA20562[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td=bgcolor:#FFFF00]WA13381N-6G[/td][td=bgcolor:#FFFF00]WA13381[/td][td=bgcolor:#FFFF00]N-6G[/td][td][/td][td][/td][td]WA13381[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet4[/td][/tr][/table]
 
Upvote 0
Yongle said:
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
Click to expand...
Nice one thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Formula Causes Memory Leak...Sometimes?!? Help me diagnose
Replies
5
Views
234
Tarkemelion
T
D
  • Question Question
VBA to Remove Text Characters From Strings
Replies
5
Views
628
Fuji
F
M
  • Solved
VBA Help - Duplicate & Unwanted Data Removal
Replies
6
Views
880
MoonDancer
M
dezibluenose
  • Solved
Countifs formula not picking up dates, when reference columns show as dates?!
Replies
7
Views
404
Alex Blakenburg
A
L
  • Question Question
SUM except the strikethrough numbers? - NON_VBA
Replies
8
Views
610
_Landon
L

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

Share this page

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