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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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
288
Tarkemelion
T
D
  • Question Question
VBA to Remove Text Characters From Strings
Replies
5
Views
689
Fuji
F
J
  • Solved
Speeding up VBA
Replies
9
Views
153
jvoss
J
M
  • Solved
VBA Help - Duplicate & Unwanted Data Removal
Replies
6
Views
928
MoonDancer
M
dezibluenose
  • Solved
Countifs formula not picking up dates, when reference columns show as dates?!
Replies
7
Views
419
Alex Blakenburg
A

Forum statistics

Threads
1,223,886
Messages
6,175,194
Members
452,616
Latest member
intern444

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