Search and replace blanks

[SamanthaK]

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!

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
> 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!

[SamanthaK]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
>
>
>

[SamanthaK]

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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]

Hi
try

Please Login or Register  to view this content.

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!

[Norman Jones]

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

[Jenni_Sweden]

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
>
>
>

[Norman Jones]

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