Remove lines not starting with numeric character

[jack71]

Hi,

how can I remove lines within each cell (multiple lines with line breaks) that are not starting with a numeric character?

223213 - this line needs to stay chair - this line needs to be removed 9 - this line needs to stay needs to be removed

<tbody>
</tbody>

Thanks and best,
Jack

 

[Rick Rothstein]

Here is a UDF (user defined function) that you can use...

Function NumStarts(S As String) As String
  Dim X As Long, Arr As Variant, Nums As Variant
  Arr = Split(S, vbLf)
  For X = 0 To UBound(Arr)
    If Arr(X) Like "[!0-9]*" Then Arr(X) = ""
  Next
  NumStarts = Join(Arr, vbLf)
  Nums = Split("121 13 5 3 3 2")
  For X = 0 To 5
    NumStarts = Replace(NumStarts, String(Nums(X), vbLf), vbLf)
  Next
  If Right(NumStarts, 1) = vbLf Then NumStarts = Left(NumStarts, Len(NumStarts) - 1)
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NumStarts just like it was a built-in Excel function. For example,

=NumStarts(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Peter_SSs]

Welcome to the MrExcel board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules. Be sure to follow & read the link at the end of the rule too!

Cross posted at: https://www.excelforum.com/excel-programming-vba-macros/1285270-remove-specific-lines-within-cell.html

If you do cross-post in the future and also provide a link, then there shouldn’t be a problem.

 

[Peter_SSs]

Here is a macro you can try. It assumes data in column A, starting at row 2 and results into column B.
If you want to overwrite the original data with the new, just remove the red part of the code.

Sub Remove_Lines()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.MultiLine = True
  RX.Pattern = "^\D.*?(" & vbLf & "|$)"
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      a(i, 1) = RX.Replace(a(i, 1), "")
    Next i
    .Offset(, 1).Value = a
  End With
End Sub

My sample data in A, result of code in B

Excel Workbook
	A	B
1	Data
2	223213 - this line needs to staychair - this line needs to be removed9 - this line needs to stayneeds to be removed	223213 - this line needs to stay9 - this line needs to stay
3	x23213 - this line now needs to gochair - this line needs to be removed9 - this line needs to stayneeds to be removed	9 - this line needs to stay
4	abcd
5
6	2342 text	2342 text
7	23 stay45 also stay	23 stay45 also stay
Remove Lines

 

[jack71]

Re: Remove lines not starting with numeric character - SOLVED

Hi,

I'm very sorry for cross-posting. Now read all the rules and will keep in mind next time.

Solution works perfectly. Thank you very much for quick and prompt solution.

Best,
Jack

 

[Rick Rothstein]

Re: Remove lines not starting with numeric character - SOLVED

Solution works perfectly.

Solution singular? Two solutions have been offered to you... which one are you referring to? did you try the other one?

 

[jack71]

Hi Rick,

thank you again both of you for your effort and sorry for late answer.

Both solutions with UDF and VBA works perfectly.

Thanks and best,
Jack

 

[Peter_SSs]

Both solutions with UDF and VBA works perfectly.

Thanks and best,
Jack

You are very welcome.