Removing special characters from text entered in a cell

[surya prakash]

Hi,
Q1) I am wondering if it is possible to remove the special characters (such as line breaks) entered in a cell.

Q2) Is it possible to extract only numeric values in the text as shown in the attachment..

thanks

[mikerickson]

=CLEAN will remove the non-printing characters.

[surya prakash]

thanks a lot mike, clean works just fine...

I am wondering if there a programmatic way of extracting the numbers from the string?

[mikerickson]

Text to Columns will get things in columns, where the VALUE function might be used.

[rory]

Given that layout, select the data in column A, then run this macro:
[VBA]Sub ExtractNums()
Dim rngCell As Range
Dim varData, varItem
Dim lngCol As Long
For Each rngCell In Selection
lngCol = 2
varData = Replace$(rngCell.Value, vbCrLf, "x")
varData = Split(varData, "x")
For Each varItem In varData
If Len(Trim(varItem)) > 0 Then
rngCell.Offset(0, lngCol).Value = Val(Trim(varItem))
lngCol = lngCol + 1
End If
Next varItem
Next rngCell
End Sub
[/VBA]

[RichardSchollar]

Here's a take on a UDF you could use:

Function GetNum(s As String, Optional instance As Long = 1) As Double
With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "\d+"
    GetNum = .Execute(s)(instance - 1)
End With
End Function

Use in a cell (eg C1 copied across to F1) like:

=GetNum($A1,COLUMNS($A:A))

Richard

[surya prakash]

Thank you Rory and Richard for your prompt response.
Will check your solution on the problem and revert back.

thanks again...

[surya prakash]

Given that layout, select the data in column A, then run this macro:
[vba]Sub ExtractNums()
Dim rngCell As Range
Dim varData, varItem
Dim lngCol As Long
For Each rngCell In Selection
lngCol = 2
varData = Replace$(rngCell.Value, vbCrLf, "x")
varData = Split(varData, "x")
For Each varItem In varData
If Len(Trim(varItem)) > 0 Then
rngCell.Offset(0, lngCol).Value = Val(Trim(varItem))
lngCol = lngCol + 1
End If
Next varItem
Next rngCell
End Sub
[/vba]

Hi Rory

You have used Replace$ function in the quote code; can you please explain the significance of using $ symbol.

thanks

[rory]

A lot of text functions in VBA have variant and string versions (e.g. Left and Left$). If you are manipulating strings, then using the string version is slightly quicker, though you probably won't notice it.

[surya prakash]

thank you Rory...