Remove all before first lower case character

[xluserg]

Hi all

How would I remove everything before a lower case character


In column M
I have starting at M10:60






example


3263 7 h
3502U- 10 ht
445334 11 t
35-409 6
3-355P 7 w 1 v
936423 7
120-PP 9
7-0565 4
/34303 9 h 1
P78062 8
75566F 4 b
/34-PU 8 t




would leave




h
ht
t
blank cell
w 1 v
blank cell
blank cell
blank cell
h 1
blank cell
b
t




Thanks all in advance

Graham

 

[Eric W]

Try:

	M	N
3263 7 h	h
3502U- 10 ht	ht
445334 11 t	t
35-409 6
3-355P 7 w 1 v	w 1 v
936423 7
120-PP 9
7-0565 4
/34303 9 h 1	h 1
P78062 8
75566F 4 b	b
/34-PU 8 t	t

<tbody>
[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]20[/TD]

[TD="align: center"]21[/TD]

</tbody>

Sheet12​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N10[/TH]
[TD="align: left"]=IFERROR(MID(M10,AGGREGATE(15,6,FIND(ROW(INDIRECT("97:122"))),M10),1),LEN(M10)),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott Huish]

Your formula is missing the CHAR function:
=IFERROR(MID(A1,AGGREGATE(15,6,FIND(CHAR(ROW(INDIRECT("97:122"))),A1),1),LEN(A1)),"")

I'm curious though why 5 (MIN) doesn't work as the function for AGGREGATE

 

[Eric W]

Ugh! Thanks for catching that Scott! My HTML Maker has been acting weird, it sometimes adds/deletes characters. I normally double check, but I guess I missed it this time. That is the right formula.

And 5 (MIN) doesn't work in AGGREGATE because the function codes less than 14 only work on ranges of data (A1:A10), not arrays {1,2,3}.

 

[Peter_SSs]

In case you might be interested in a fairly succinct user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Function Fromlc(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Pattern = "[a-z]"
    If .Test(s) Then Fromlc = Mid(s, .Execute(s)(0).FirstIndex + 1)
  End With
End Function

Excel 2016
	M	N
10	3263 7 h	h
11	3502U- 10 ht	ht
12	445334 11 t	t
13	35-409 6
14	3-355P 7 w 1 v	w 1 v
15	936423 7
16	120-PP 9
17	7-0565 4
18	/34303 9 h 1	h 1
19	P78062 8
20	75566F 4 b	b
21	/34-PU 8 t	t
Remove before lower case

Cell Formulas
Range	Formula
N10	=Fromlc(M10)

 

[xluserg]

Hi Guys

The formula works great and Peter's function works great. I think I'll use the function for now.

Thanks Eric, Scott and Peter.

All the best

Graham

 

[Peter_SSs]

Thanks Eric, Scott and Peter.

All the best

Graham

You're welcome. Good that you have choices.

 

[Scott Huish]

With a small change to the pattern, you could just pull the first value from the .Execute

Function Fromlc(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Pattern = "[a-z].*"
    If .Test(s) Then Fromlc = .Execute(s)(0)
  End With
End Function

 

[Peter_SSs]

With a small change to the pattern, you could just pull the first value from the .Execute

Good change.