PDA

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?

pike
03-07-2015, 12:45 AM
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

pike
03-10-2015, 12:59 AM
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

pike
03-10-2015, 11:53 PM
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

jonh
03-11-2015, 02:53 AM
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

jonh
03-11-2015, 08:59 AM
@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'.)

jonh
03-11-2015, 09:48 AM
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

pike
03-12-2015, 12:13 AM
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!

pike
03-12-2015, 01:59 AM
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