PDA

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

SamT
11-13-2019, 11:53 PM
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

snb
11-14-2019, 01:04 AM
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

snb
11-14-2019, 08:58 AM
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

snb
11-14-2019, 01:39 PM
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

snb
11-15-2019, 02:00 AM
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:

OBP
11-15-2019, 02:05 PM
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!!

SamT
11-15-2019, 08:27 PM
@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?

OBP
11-16-2019, 01:32 AM
Sam, it wasn't aimed at you.:)

Zack, How are you doing, we haven't spoken in years.
Are you still a first responder?

SamT
11-16-2019, 02:53 AM
:thumb