View Full Version : macro to remove numbers before first alphabet in a cell
parag7819
03-06-2015, 11:15 PM
H, to all
I am trying to find a way to remove all numbers before the first alphabet in a range of cells.
for eg.
if a cell value is
1180. 1980 Vauxhall Viceroy 4 door sedan/saloon FR 4M 2490 cm³ 116 PS/114 bhp/85 kW 1230 kg
then the adjacent cell value should be
Vauxhall Viceroy 4 door sedan/saloon FR 4M 2490 cm³ 116 PS/114 bhp/85 kW 1230 kg.
there is no specific as to the length of numbers.
Can anyone help me please?
Hi parag7819 (http://www.vbaexpress.com/forum/member.php?55243-parag7819)
use
=MID(A1,MIN(INDEX(SEARCH(CHAR(64+ROW($1:$26)),A1&"abcdefghijklmnopqrstuvwxyz"),0)),LEN(A1))
Yongle
03-09-2015, 10:57 PM
:)Thank you @pike for making my solution look a little verbose
Sub First_Char_Check()
'set up variables etc
Dim a As Integer, c As Integer, r As Integer
Dim Char1 As String, CellString As String, str As String
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
MsgBox LastRow & " LastRow"
'as illustration range is column A
MyArray = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z")
For c = 1 To 1
For r = 2 To LastRow ' assumes header in row 1
str = Cells(r, c).Value
Char1 = Left(str, 1)
CellString = Cells(r, c).Value
'check if first character of cell is alphabetic or not and keep removing 1st character until it is
Do While a <> 9999 ' arbitrary big enough number that will never be reached within the loop
If IsInArray(Char1, MyArray) = True Then
a = 9999
Cells(r, c + 1).Value = CellString
Else
CellString = Mid(CellString, 2)
Char1 = Left(CellString, 1)
a = a + 1
End If
Loop
a = 1
Next r
Next c
End Sub
Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function
Variation macro
Option Explicit
Sub First_Char_Check()
Dim rRow As Integer, Char1 As Integer
For rRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row
For Char1 = 1 To Len(Cells(rRow, 1).Value)
If Mid(Cells(rRow, 1).Value, Char1, Len(Cells(rRow, 1).Value)) Like "[a-zA-Z]*" Then
Cells(rRow, 1).Value = Mid(Cells(rRow, 1).Value, Char1, Len(Cells(rRow, 1).Value))
Exit For
End If
Next Char1
Next rRow
End Sub
Yongle
03-10-2015, 02:04 AM
Thanks @pike - good to see how to translate own code from vbBaby to vbGrownUpSpeak
Hi Yongle
They are all good codes
here is another variation to consider
option explicit
Sub First_Char_Check()
Dim rRow As Integer, Char1 As Integer, myArray
For rRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row
myArray = Split(StrConv(Cells(rRow, 1).Value, vbUnicode), vbNullChar)
For Char1 = 0 To UBound(myArray)
If myArray(Char1) Like "[a-zA-Z]" Then
Cells(rRow, 1).Offset(0, 1).Value = Mid(Cells(rRow, 1).Value, Char1 + 1, Len(Cells(rRow, 1).Value))
Exit For
End If
Next Char1
Next rRow
End Sub
Public Function StripNum(s As String) As String
Dim i As Integer
Do Until i > Len(s)
i = i + 1
If Mid(s, i, 1) Like "[a-zA-Z]" Then Exit Do
Loop
StripNum = Mid(s, i)
End Function
Yongle
03-11-2015, 08:38 AM
@pike @jonh
Thank you both for the "menu" of options. Talk about being spoilt for choice. Looking forward to doing my homework and getting to grips with this wonderful new world. I particularly like the flexibility of like "[a-zA-Z]" - but what happens if I want to use 0-9 and 12-19 - like "[0-912-19"] does not look promising
Aflatoon
03-11-2015, 08:48 AM
You can also use a byte array:
Sub First_Char_Check()
Dim rRow As Long
Dim Char1 As Long
Dim myArray() As Byte
For rRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row
myArray = UCase$(Cells(rRow, 1).Value)
For Char1 = 0 To UBound(myArray) Step 2
Select Case myArray(Char1)
Case 65 To 90
Cells(rRow, 1).Offset(0, 1).Value = Mid$(Cells(rRow, 1).Value, (Char1 + 1) / 2)
Exit For
End Select
Next Char1
Next rRow
End Sub
@Yongle - I liked it too, that's why I nicked it off pike ;)
Yongle
03-11-2015, 09:22 AM
@Aflatoon (http://www.vbaexpress.com/forum/member.php?24778-Aflatoon) - fascinated by yet another alternative. Feels a bit like I'm living inside a multi-dimensional array myself at the moment!
I have never used select case - only noticed it within anyone's coding a few times - but I assume Case 65 To 90 must be A-Z, so what would the syntax be for A, C, E and 4 - not the character numbers - what do we use as separators etc? Do we list inside an array separated by commas?
thanks
Aflatoon
03-11-2015, 09:45 AM
You'd use:
Case 52, 65, 67, 69
(52 being the ASCII character code for the digit '4'.)
or
case asc("A"), asc("C"), asc("E"), asc("4")
if you want it easier to read.
Public Function StripNum(s As String) As String
s = Trim(s)
Do
t = Val(s)
If t = 0 And Left(s, 1) <> "0" Then Exit Do Else s = Trim(Mid(s, Len(t) + 1))
Loop
StripNum = s
End Function
I pinched it of microsoft. link to Like Operator (Visual Basic) (https://msdn.microsoft.com/en-us/library/te33kb6t.aspx)
Yongle
03-12-2015, 01:23 AM
Plagarism with pride - love it!
not as powerful as regular expression .. but in vba its quick
if cell like "[0-9]" or cell like "1[2-9]" then .....
Yongle
03-12-2015, 03:00 AM
@pike - I was puzzled by
like "1[2-9]" but now I get it
- what a clever way to shortcut so many options eg
cell like "[A-Za-z][1-3][1-9]"
Wow and ow!
This is one of those "if only I had known that then" moments
- a few months ago when I was trying to solve one particular quandary - I had so many nested brackets inside "AND", "IF" and "OR" formula that I got myself in real knots and Excel kept moaning that it could not cope with all the brackets and combination of conditions.
What a lot of unnecessary time lost that could have been spent drinking beer or something!
What is the easiest way of excluding a particular value from this?
Say, using
cell like "[A-Za-z][1-3][1-9]" but exclude the values a12 and Z29
Aflatoon
03-12-2015, 04:43 AM
It's probably easiest to just And in a couple of tests for those two values.
parag7819
03-19-2015, 10:44 PM
Pike, I thank you for the valuable and educating help.
Thank you all for your assistance.
I have one more question now.
I used =MID(A1,MIN(INDEX(SEARCH(CHAR(64+ROW($1:$26)),A1&"abcdefghijklmnopqrstuvwxy z"),0)),LEN(A1))
on cell A1 (1180. 1980 Vauxhall Viceroy 4 door sedan/saloon FR 4M 2490 cm³ 116 PS/114 bhp/85 kW 1230 kg)
to get
(Vauxhall Viceroy 4 door sedan/saloon FR 4M 2490 cm³ 116 PS/114 bhp/85 kW 1230 kg) in cell B1,
However is their any modification for this where I get (1180.1980) in cell B1 and
(Vauxhall Viceroy 4 door sedan/saloon FR 4M 2490 cm³ 116 PS/114 bhp/85 kW 1230 kg) in cell C1
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.