VBA parsing alphanumeric text string

[Atokad27]

Hey everyone,

First time posting anything and very new to VBA as well. I gathered boatloads of research and watched countless videos in order to do something very specific.

I have hit a wall & cannot seem to find the correct solution. I have found something that is VERY close to a solution these forums but not a perfect fit for the data I am trying to extract.

Reference post - (vba find first set of numbers ignoring any subsequent post)

In an alphanumeric string I would would like to extract all text up to the first numeric value, then remove everything after that numeric value. I’ve tried a bunch of different things on my own, but they haven’t worked in every scenario case. Below are generic examples of data & the values I’m looking to return

1.Dogworld 12 Andrew Hopper special edition variant 526
- return dogworld 12

2.Dogworld 12 generic edition WGC
- return Dogworld 12

3. Radioworld 139 edition home 30
- return Radioworld 139

4. Radioworld 139 Harry Testiburger limited sketch variant
- return Radioworld 139

5. NewYorkreview 8 Scott Tenemen best parental Chili edition
- return NewYorkreview 8
6.
7.
8.

So on and so forth
All I need is the title & the first numeric value which denotes it apart, because I can have 2 bits of data in the same spreadsheet with the same title, So I must distinguish them, only the very first numeric value is necessary & nothing after

If I could I would also return the values with no spaces but, that is just a request.
Example of that would be dogworld12 from above

Any help would be phenomenal! I cannot post real world data onto a forum or anywhere for that matter, else I would gladly share it.
Thank you

 

[Rick Rothstein]

You are using XL365, do you really need to be using VBA for this question? Assuming the examples you showed were in cells A1:A5, this single formula would output the full list you are seeking...

=TOCOL(LEFT(A1:A5,FIND(SEQUENCE(,10,0)&" ",A1:A5)),3)

 

[Peter_SSs]

I don't know how representative your sample data is but for that sample data (always a space immediately after the first set of digits and always only 1 space before those digits), another formula approach would be this

Atokad27.xlsm
	A	B
1	Dogworld 12 Andrew Hopper special edition variant 526	Dogworld12
2	Dogworld 12 generic edition WGC	Dogworld12
3	Radioworld 139 edition home 30	Radioworld139
4	Radioworld 139 Harry Testiburger limited sketch variant	Radioworld139
5	NewYorkreview 8 Scott Tenemen best parental Chili edition	NewYorkreview8
Sheet1

Cell Formulas
Range	Formula
B1:B5	B1	=SUBSTITUTE(TEXTBEFORE(A1:A5," ",2)," ","")
Dynamic array formulas.

For vba approaches:

If only 1 space before the digits

Sub Get_Text_1_Space()
  With Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .Cells(1).Formula2 = "=SUBSTITUTE(TEXTBEFORE(" & .Offset(, -1).Address & ","" "",2),"" "","""")"
    .Value = .Value
  End With
End Sub

If there could be multiple spaces (or none) before the digits, then adapting Rick's approach to vba and adding your request to eliminate any spaces.

Sub Get_Text_Multiple_Spaces()
  With Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .Cells(1).Formula2 = Replace("=TOCOL(SUBSTITUTE(LEFT(#,FIND(SEQUENCE(,10,0)&"" "",#)),"" "",""""),3)", "#", .Offset(, -1).Address)
    .Value = .Value
  End With
End Sub

 

[Atokad27]

ahhhh,
I was hoping that wouldn’t happen.
Your 100% correct my random data is a poor representation of the data I am trying to parse. I will get you a older singular column of the actual data. As I am almost pretty sure that solution is good, just not what I am after.

I would like for it to be in VBA as All of my other data combing & prep to prepare the whole dataset are coded there, but that’s a preference of mine, if you can do it without, I am happy with that too.

Hang tight 20 mins need to get laptop

 

[Atokad27]

 

[Peter_SSs]

Any chance that you could cut that down to say 10-15 rows, enter the wanted results manually and post with XL2BB so that we can copy to test with?
Be sure to include an example like this one

 

[Atokad27]

Yes will do might work a couple hours as I am now currently working. I will include ones like 2.5 or extra numbers to the right as they were the ones messing up my best solution I’ve come up with so far.