Search and replace blanks
 

How do you search an entier spreadsheet and do the following:

1) Check if the contents of a cell is numeric?
2) If content is numeric then search for blanks (" ") and replace them with
nothing (""). E.g. I hace a number in a cell written like: 1000 22 458 but
want to convert it to 100022458. I do not want text like: 'Market value' to
be replaced by 'Marketvalue'.

Please help me on this one!!!
--
I am very thankful for any fast assistance!

Search and replace blanks
 

Hi Samantha,

Try:

'=============
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range

Set WB = Workbooks("YourBook.xls") '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE
Set rng = SH.Range("A1:D30") '<<===== CHANGE

For Each rCell In rng.Cells
With rCell
If Not IsEmpty(.Value) Then
If Not .Value Like "*[A-Z]*" Then
.Replace What:=" ", Replacement:=""
End If
End If
End With
Next rCell

End Sub
'<<=============

--
---
Regards,
Norman



"SamanthaK" wrote in message
...
How do you search an entier spreadsheet and do the following:

1) Check if the contents of a cell is numeric?
2) If content is numeric then search for blanks (" ") and replace them
with
nothing (""). E.g. I hace a number in a cell written like: 1000 22 458 but
want to convert it to 100022458. I do not want text like: 'Market value'
to
be replaced by 'Marketvalue'.

Please help me on this one!!!
--
I am very thankful for any fast assistance!

Search and replace blanks
 

Great!! Thanxxx! Is there any way to refer to the entier Worksheet (I want to
check all cells) without writing a large range?
--
I am very thankful for any fast assistance!


"Norman Jones" skrev:

Hi Samantha,

Try:

'=============
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range

Set WB = Workbooks("YourBook.xls") '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE
Set rng = SH.Range("A1:D30") '<<===== CHANGE

For Each rCell In rng.Cells
With rCell
If Not IsEmpty(.Value) Then
If Not .Value Like "*[A-Z]*" Then
.Replace What:=" ", Replacement:=""
End If
End If
End With
Next rCell

End Sub
'<<=============

--
---
Regards,
Norman



"SamanthaK" wrote in message
...
How do you search an entier spreadsheet and do the following:

1) Check if the contents of a cell is numeric?
2) If content is numeric then search for blanks (" ") and replace them
with
nothing (""). E.g. I hace a number in a cell written like: 1000 22 458 but
want to convert it to 100022458. I do not want text like: 'Market value'
to
be replaced by 'Marketvalue'.

Please help me on this one!!!
--
I am very thankful for any fast assistance!

Search and replace blanks
 

Hmmm I am having another problem. Even cells containing just text are
"trimmed" e.g. market value does become marketvalue. is there any way to
avoid this? Pleaseeeeeeeeee! ;-)
--
I am very thankful for any fast assistance!


"SamanthaK" skrev:

Great!! Thanxxx! Is there any way to refer to the entier Worksheet (I want to
check all cells) without writing a large range?
--
I am very thankful for any fast assistance!


"Norman Jones" skrev:

Hi Samantha,

Try:

'=============
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range

Set WB = Workbooks("YourBook.xls") '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE
Set rng = SH.Range("A1:D30") '<<===== CHANGE

For Each rCell In rng.Cells
With rCell
If Not IsEmpty(.Value) Then
If Not .Value Like "*[A-Z]*" Then
.Replace What:=" ", Replacement:=""
End If
End If
End With
Next rCell

End Sub
'<<=============

--
---
Regards,
Norman



"SamanthaK" wrote in message
...
How do you search an entier spreadsheet and do the following:

1) Check if the contents of a cell is numeric?
2) If content is numeric then search for blanks (" ") and replace them
with
nothing (""). E.g. I hace a number in a cell written like: 1000 22 458 but
want to convert it to 100022458. I do not want text like: 'Market value'
to
be replaced by 'Marketvalue'.

Please help me on this one!!!
--
I am very thankful for any fast assistance!

Search and replace blanks
 

Hi
try

Code:
--------------------

Sub test()
Dim r As Range
With CreateObject("VBScript.RegExp")
.Pattern = "^(\d+\s?)+$"
For Each r In Activesheet.UsedRange
If .Test(r.Value) Then r.Value = Val(r.Value)
Next
End With
End Sub
--------------------


SamanthaK Wrote:
How do you search an entier spreadsheet and do the following:

1) Check if the contents of a cell is numeric?
2) If content is numeric then search for blanks (" ") and replace them
with
nothing (""). E.g. I hace a number in a cell written like: 1000 22 458
but
want to convert it to 100022458. I do not want text like: 'Market
value' to
be replaced by 'Marketvalue'.

Please help me on this one!!!
--
I am very thankful for any fast assistance!


--
jindon
------------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135
View this thread: http://www.excelforum.com/showthread...hreadid=552144

Search and replace blanks
 

Hi Samantha,

Great!! Thanxxx! Is there any way to refer to the entier Worksheet (I want
to
check all cells) without writing a large range?

Change:

Set rng = SH.Range("A1:D30") '<<===== CHANGE

to

Set rng = SH.UsedRange

Hmmm I am having another problem. Even cells containing just text are
"trimmed" e.g. market value does become marketvalue. is there any way to
avoid this? Pleaseeeeeeeeee! ;-)

Change:

If Not .Value Like "*[A-Z]*" Then

to
If Not UCase(.Value) Like "*[A-Z]*" Then

---
Regards,
Norman

Search and replace blanks
 

Thank you so much!!!! They both work but when I use

Set rng = SH.UsedRange

instead of writing

Set rng = SH.Range("A1:D30")

the processing time becomes very very long. Why is that?
--
I am very thankful for any fast assistance!


"Norman Jones" skrev:

Hi Samantha,

Great!! Thanxxx! Is there any way to refer to the entier Worksheet (I want
to
check all cells) without writing a large range?

Change:

Set rng = SH.Range("A1:D30") '<<===== CHANGE

to

Set rng = SH.UsedRange

Hmmm I am having another problem. Even cells containing just text are
"trimmed" e.g. market value does become marketvalue. is there any way to
avoid this? Pleaseeeeeeeeee! ;-)

Change:

If Not .Value Like "*[A-Z]*" Then

to
If Not UCase(.Value) Like "*[A-Z]*" Then

---
Regards,
Norman

Search and replace blanks
 

Hi Samantha,

Set rng = SH.UsedRange

instead of writing

Set rng = SH.Range("A1:D30")

the processing time becomes very very long. Why is that?

The used range is 9presumably) much larger than the previously specified
range.

If you are unable to restrict the range to specific columns . rows, perhaps
try:

'=============
Public Sub Tester2()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range
Dim CalcMode As Long
Dim ViewMode As Long

Set WB = Workbooks("YourBook3.xls") '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE

On Error Resume Next
Set rng = SH.UsedRange. _
SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo XIT

If Not rng Is Nothing Then
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveWindow
ViewMode = .View
.View = xlNormalView
End With

SH.DisplayPageBreaks = False

For Each rCell In rng.Cells
With rCell
rCell.Select
If Not IsEmpty(.Value) Then
If Not UCase(.Value) Like "*[A-Z]*" Then
.Replace What:=" ", Replacement:=""
End If
End If
End With
Next rCell
End If

XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

ActiveWindow.View = ViewMode

End Sub
'<<=============


---
Regards,
Norman