View Full Version : Splitting up a String
Saima786
11-13-2019, 04:31 PM
Hi All
I have the following data the in cells A1 and A2
123456 - Summer 2020 - Norwich
345667 Holiday - 2019 Liverpool
How can I via VBA delete all cell data until it gets to the 1st Alpha Character so I would now get
Summer 2020 - Norwich
Holiday - 2019 Liverpool
So effectively the code would look it each cell and when it gets to first alpha Char it would split it this point
Any help would be appreciated
Zahed
p45cal
11-13-2019, 05:27 PM
Before running the following macro, select the cells you want to be processed:
Sub blah()
For Each cll In Selection.Cells
For i = 1 To Len(cll.Value)
ac = Asc(Mid(UCase(cll.Value), i, 1))
If ac > 64 And ac < 91 Then
cll.Value = Mid(cll.Value, i)
Exit For
End If
Next i
Next cll
End Sub
RegEx would probably be better but I don't know it well enough.
大灰狼1976
11-13-2019, 10:42 PM
Hi Saima!
Using RegExp methods:
Sub test()
Dim reg As Object, rng As Range, arr, i&
arr = Array("123456 - Summer 2020 - Norwich", "345667 Holiday - 2019 Liverpool")
Set reg = CreateObject("vbscript.regexp")
With reg
.Pattern = "^[^A-z]+"
For i = 0 To 1
MsgBox .Replace(arr(i), "")
Next i
End With
End Sub
Option Explicit
Sub RemoveFirst()
Dim i As Long
Dim arr
Dim tmp As String
Dim Cel As Range
For Each Cel In Selection
On Error GoTo CelNext
arr = Split(Cel, " - ")
For i = LBound(arr) + 1 To UBound(arr)
tmp = tmp + arr(i)
If i < UBound(arr) Then tmp = tmp + " - "
Next i
CelNext:
Next Cel
Cel = tmp
End Sub
a UDF
Function F_snb(c00)
F_snb = Mid(c00, Len(CStr(Val(c00))) + 1)
End Function
In B1: =F_snb(A1)
In B2: =F_snb(A2)
a Macro:
Sub M_snb()
sn = Cells(1).CurrentRegion
For j = 1 To UBound(sn)
sn(j, 1) = Trim(Mid(sn(j, 1), Len(CStr(Val(sn(j, 1)))) + 1))
Next
Cells(1, 5).Resize(UBound(sn)) = sn
End Sub
Tom Jones
11-14-2019, 08:28 AM
@snb
Op want this answer:
Summer 2020 - Norwich
Holiday - 2019 Liverpool
your cod result is:
- Summer 2020 - Norwich
Holiday - 2019 Liverpool
Then you or TS should amend the code. :yes
Tom Jones
11-14-2019, 09:39 AM
snb,
If you help someone don't ask for "money" :D
We don't provide solutions in Fora, only suggestions to stimulatie TS to find her/his own approach.
Zack Barresse
11-14-2019, 05:29 PM
@Saima, if your solution will always have a " - " after a set of numbers and before the text you want, SamT's solution will work just fine. Don't mind snb, they're always cantankerous. :giggle
For those who lacked the imagination:
Sub M_snb()
sn = Cells(1).CurrentRegion
For j = 1 To UBound(sn)
sn(j, 1) = Replace(Trim(Replace(Mid(sn(j, 1), Len(CStr(Val(sn(j, 1)))) + 1), " - ", " ")), " ", " - ")
Next
Cells(1, 5).Resize(UBound(sn)) = sn
End Sub
also applicable to the UDF.
Zack Barresse
11-15-2019, 08:52 AM
For those who lacked the proper imagination, we would add this:
Dim sn As Variant
Dim j As Long
paulked
11-15-2019, 09:03 AM
Now now boys :whistle:
Zack, how about being really pedantic and asking for some decent Error Trapping while you are at it.
And what about the possibility of Empty Cells, will they break the code?
Incorrect use of Null?
Zack Barresse
11-15-2019, 04:42 PM
@OBP: what is this madness you speak of?!?! Heresy!!
@OBP:
decent Error Trapping while you are at it.
And what about the possibility of Empty Cells, will they break the code?
I thought I handled all that. No?
Sam, it wasn't aimed at you.:)
Zack, How are you doing, we haven't spoken in years.
Are you still a first responder?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.