PDA

View Full Version : Removing special characters from text entered in a cell



surya prakash
12-05-2007, 12:37 AM
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
12-05-2007, 12:48 AM
=CLEAN will remove the non-printing characters.

surya prakash
12-05-2007, 01:06 AM
thanks a lot mike, clean works just fine...

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

mikerickson
12-05-2007, 06:39 AM
Text to Columns will get things in columns, where the VALUE function might be used.

rory
12-05-2007, 06:54 AM
Given that layout, select the data in column A, then run this macro:
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

RichardSchollar
12-05-2007, 11:52 AM
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
12-06-2007, 12:03 AM
Thank you Rory and Richard for your prompt response.
Will check your solution on the problem and revert back.

thanks again...

surya prakash
12-06-2007, 05:44 AM
Given that layout, select the data in column A, then run this macro:
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


Hi Rory

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

thanks

rory
12-06-2007, 05:50 AM
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
12-06-2007, 06:28 AM
thank you Rory...