Remove Everything When Numbers End To Adjacent Column

Dazzawm

Well-known Member
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

Active Member
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

Dazzawm

Well-known Member
Sorry does not do anything.

Yongle

Well-known Member
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
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
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
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
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
Are you over-complicating

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
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