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.
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
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...
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.