Solved: Delete Text to Left of First Number

[Mcygee]

Hello everybody!

This should be a simple issue, but I have yet to dig down into the world of string manipulation.

I need to remove everything to the left of the first number in a string. I'll loop it to do this to each row in a specific column. Here is some data examples...

BDKS-700e
BBSS_4432
BMDK431b

So I would want those to turn into...

700e
4432
431b

Any help would be appreciated. Even a link to a good read on string manipulation that would allow me to figure this out on my own.

Thanks!

[p45cal]

Using just worksheet formulae, array enter the likes of:
=MID(D18,MIN(IF(ISNUMBER(VALUE(MID(D18,ROW($1:$10),1))),ROW($1:$10))),100)
it seems a bit long to me - I'm not sure my head's screwed on properly tonight - I feel there ought to be a much more straightforward solution.

I note you mention looping, so vba solution to follow...

and here it is, for the currently selected cells:
[vba]Sub blah()
For Each cll In Selection.Cells
For i = 1 To Len(cll.Value)
If IsNumeric(Mid(cll.Value, i, 1)) Then Exit For
Next i
If i > 1 Then cll.Value = Mid(cll.Value, i)
Next cll
End Sub
[/vba]

[GTO]

Greetings,

I took this the same as p45cal, that you are wanting to change the vals in place.

I am awfully new to Regular Expressions, but I believe this will rip everything until it hits a digit.
[vba]
Option Explicit

Sub exa()
Dim _
rngColPartial As Range, _
rCell As Range
'// Selection used for example, change to suit. //
Set rngColPartial = Selection

With CreateObject("VBScript.RegExp")
.Global = False
.Pattern = "[^\d]+"
For Each rCell In rngColPartial
rCell.Value = .Replace(rCell.Value, vbNullString)
Next
End With
End Sub

[/vba]

If by chance this will be for thousands of rows, I would consider flipping the range into and array, fixing there, and flipping back.

Hope that helps,

Mark

[Bob Phillips]

Using just worksheet formulae, array enter the likes of:
=MID(D18,MIN(IF(ISNUMBER(VALUE(MID(D18,ROW($1:$10),1))),ROW($1:$10))),100)
it seems a bit long to me - I'm not sure my head's screwed on properly tonight - I feel there ought to be a much more straightforward solution.

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),255)

non-array entered.

[Mcygee]

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),255)

does exactly what I requested.

However, I just realized I have some data that this wouldn't work on. My fault for not looking at all the data.

Maybe this formula could be modified slightly to get the new results I need?

Here is some more sample data...

KKJS_342e-General
LLDS453-Mini
DDS665a_Mini
AFE448e

Need that data to be...

342e
453
665a
448e

So basically everything to the left of the first number, but then everything to the right of the end of the number except for the first letter if there is one. In my data there should always be a - or _ separating the number or number and first letter from the General or Mini or whatever.

Maybe one string couldn't do it, but possibly two formulas? For instance, I could use the current string that works to make it...

342e-General
453-Mini
665a_Mini
448e

And then once I run...

[vba]Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False[/vba]
It will remove the formula and leave only the value. Then I could apply a new formula to edit the right side of my data.

Any ideas on a new single formula or a new formula for the right side of the data?

THANKS!!!!

[p45cal]

Well, if you're going to use a macro at all, you may as well have it doing the whole job in one hit. This first one does the job in situ; you select the cells you want to process, run this macro and they're changed:
[vba]Sub blah2()
For Each cll In Selection.Cells
For i = 1 To Len(cll.Value)
If IsNumeric(Mid(cll.Value, i, 1)) Then Exit For
Next i
For j = i + 1 To Len(cll.Value)
If Mid(cll.Value, j, 1) = "_" Or Mid(cll.Value, j, 1) = "-" Then Exit For
Next j
cll.Value = Mid(cll.Value, i, j - i)
Next cll
Selection.NumberFormat = "@"
End Sub[/vba]
This second macro, just a variation on a theme should you not want to lose your original data, you select the cells as before, run the macro, but it leaves the selected cells intact but puts the new values in the column immediately to the right:
[vba]Sub blah()
For Each cll In Selection.Cells
For i = 1 To Len(cll.Value)
If IsNumeric(Mid(cll.Value, i, 1)) Then Exit For
Next i
For j = i + 1 To Len(cll.Value)
If Mid(cll.Value, j, 1) = "_" Or Mid(cll.Value, j, 1) = "-" Then Exit For
Next j
cll.Offset(, 1).Value = Mid(cll.Value, i, j - i)
Next cll
Selection.Offset(, 1).NumberFormat = "@"
End Sub
[/vba]

[Bob Phillips]

Try

=IF(ISNUMBER(FIND({"_"},SUBSTITUTE(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}, A1&"0123456789")),255),"-","_"))),
LEFT(SUBSTITUTE(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),2 55),"-","_"),FIND({"_"},SUBSTITUTE(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01 23456789")),255),"-","_"))-1),
MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),255))

[Mcygee]

THANKS!!!

Xld and p45cal, both of your solutions work perfectly. Also thanks for your added input GTO. I greatly appreciate your time on this. Hopefully I'll learn something new while implementing it.

[Aussiebear]

Try

=IF(ISNUMBER(FIND({"_"},SUBSTITUTE(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}, A1&"0123456789")),255),"-","_"))),
LEFT(SUBSTITUTE(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),2 55),"-","_"),FIND({"_"},SUBSTITUTE(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01 23456789")),255),"-","_"))-1),
MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),255))

(Sigh......)

It all made sense until I got past the first "IF"

[Mikey]

Are there always 3 digits?

If so you could use this formula

=SUBSTITUTE(SUBSTITUTE(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789 ")),4),"-",""),"_","")

or for a variable number of digits

=REPLACE(LEFT(A1,MIN(FIND({"_","-"},A1&"_-",MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))))-1),1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1,"")