macro to find and change text within a range of cells...

[schwammrs]

Hi all--

I'd like to be able to select a range of cells, search that range for any
cells that contain a number (format is set to "General"), and then, if a
cell contains a number, change the Format to "Text" and then add a space
after the number.

Currently, I have a conditional format set to "=ISNUMBER(cellref)", which
highlights the cell in red if true. Then I have a short little macro--listed
at the very end--that changes the Format and then adds the space. (Note: all
I know about vba is what I've looked at after recording a macro and what
I've read or found online... so I'm pretty proud that I could do at least
this much!) But I soon tired of clicking on each cell (highlighted by cond.
formt) and then clicking my macro button, so that got me wishing I could
just select the entire range and check it all at once. But that's totally
beyond my current vba capabilities.

[start short rant here...] By the way, yes, I could just set the Cell Format
for everything to "Text" but then I'd have no way to check if a "text" "4"
will be interpreted as a "number" 4 in my mail merge Word doc using ODBC
(and either changed to 4.0 or, even wose, just left blank!). And this all
wouldn't be a problem if I could do the mail merge using the default DDE
(which has worked completely fine on my computer for years now), but all of
a sudden, Word started hanging when trying to open the mail merge base
documents using DDE. Aargh! And after much online searching and reading of
newsgroups, I've only managed to determine that answers to *WHY* DDE mail
merges suddenly stop working and *HOW* on earth to fix them rank right up
there with what's the meaning of life and when will the Cubs win a World
Series again... [rant over]

Thanks for any help,
Karin
(using Office 2000, if that's important)

And here's my little macro:

Sub ChangeCellToText()
'
' ChangeCellToText Macro
' change cell format to text, then add space after number
'
Selection.NumberFormat = "@"

Dim CellText As String

CellText = ActiveCell.Text
CellText = CellText & " "
ActiveCell.Value = CellText

End Sub

 

[Guest]

Here's one that would do it and takes a 'hands-off' approach - it will select
all cells on a sheet that were ever used (within the sheet's UsedRange area)
and evaluate them. Since same cells may get evaluated more than once over
time, I added a check to keep from adding extra space characters to the end
of an already converted number:

Sub NumToText()
Dim anyCell As Object
'this is probably overkill, but
'it's quick and sure
ActiveSheet.UsedRange.Select
For Each anyCell In Selection
If IsNumeric(anyCell.Value) Then
anyCell.NumberFormat = "@"
'keep from continually adding spaces
If Right(anyCell.Text, 1) <> " " Then
anyCell.Value = anyCell.Text & " "
End If
End If
Next
Range("A1").Select ' clean up appearance
End Sub

Now, if you'd rather choose the range of cells to test yourself, then change
it to look like this and then select the cells to be tested before running
the code:
Sub NumToText()
Dim anyCell As Object
For Each anyCell In Selection
If IsNumeric(anyCell.Value) Then
anyCell.NumberFormat = "@"
'keep from continually adding spaces
If Right(anyCell.Text, 1) <> " " Then
anyCell.Value = anyCell.Text & " "
End If
End If
Next
Range("A1").Select ' clean up appearance
End Sub

Hi all--

I'd like to be able to select a range of cells, search that range for any
cells that contain a number (format is set to "General"), and then, if a
cell contains a number, change the Format to "Text" and then add a space
after the number.

Currently, I have a conditional format set to "=ISNUMBER(cellref)", which
highlights the cell in red if true. Then I have a short little macro--listed
at the very end--that changes the Format and then adds the space. (Note: all
I know about vba is what I've looked at after recording a macro and what
I've read or found online... so I'm pretty proud that I could do at least
this much!) But I soon tired of clicking on each cell (highlighted by cond.
formt) and then clicking my macro button, so that got me wishing I could
just select the entire range and check it all at once. But that's totally
beyond my current vba capabilities.

[start short rant here...] By the way, yes, I could just set the Cell Format
for everything to "Text" but then I'd have no way to check if a "text" "4"
will be interpreted as a "number" 4 in my mail merge Word doc using ODBC
(and either changed to 4.0 or, even wose, just left blank!). And this all
wouldn't be a problem if I could do the mail merge using the default DDE
(which has worked completely fine on my computer for years now), but all of
a sudden, Word started hanging when trying to open the mail merge base
documents using DDE. Aargh! And after much online searching and reading of
newsgroups, I've only managed to determine that answers to *WHY* DDE mail
merges suddenly stop working and *HOW* on earth to fix them rank right up
there with what's the meaning of life and when will the Cubs win a World
Series again... [rant over]

Thanks for any help,
Karin
(using Office 2000, if that's important)

And here's my little macro:

Sub ChangeCellToText()
'
' ChangeCellToText Macro
' change cell format to text, then add space after number
'
Selection.NumberFormat = "@"

Dim CellText As String

CellText = ActiveCell.Text
CellText = CellText & " "
ActiveCell.Value = CellText

End Sub

 

[schwammrs]

Thanks so much. I'm using the second one and it works great!

Here's one that would do it and takes a 'hands-off' approach - it will
select
all cells on a sheet that were ever used (within the sheet's UsedRange
area)
and evaluate them. Since same cells may get evaluated more than once over
time, I added a check to keep from adding extra space characters to the
end
of an already converted number:

Sub NumToText()
Dim anyCell As Object
'this is probably overkill, but
'it's quick and sure
ActiveSheet.UsedRange.Select
For Each anyCell In Selection
If IsNumeric(anyCell.Value) Then
anyCell.NumberFormat = "@"
'keep from continually adding spaces
If Right(anyCell.Text, 1) <> " " Then
anyCell.Value = anyCell.Text & " "
End If
End If
Next
Range("A1").Select ' clean up appearance
End Sub

Now, if you'd rather choose the range of cells to test yourself, then
change
it to look like this and then select the cells to be tested before running
the code:
Sub NumToText()
Dim anyCell As Object
For Each anyCell In Selection
If IsNumeric(anyCell.Value) Then
anyCell.NumberFormat = "@"
'keep from continually adding spaces
If Right(anyCell.Text, 1) <> " " Then
anyCell.Value = anyCell.Text & " "
End If
End If
Next
Range("A1").Select ' clean up appearance
End Sub

Hi all--

I'd like to be able to select a range of cells, search that range for any
cells that contain a number (format is set to "General"), and then, if a
cell contains a number, change the Format to "Text" and then add a space
after the number.

Currently, I have a conditional format set to "=ISNUMBER(cellref)", which
highlights the cell in red if true. Then I have a short little
macro--listed
at the very end--that changes the Format and then adds the space. (Note:
all
I know about vba is what I've looked at after recording a macro and what
I've read or found online... so I'm pretty proud that I could do at least
this much!) But I soon tired of clicking on each cell (highlighted by
cond.
formt) and then clicking my macro button, so that got me wishing I could
just select the entire range and check it all at once. But that's totally
beyond my current vba capabilities.

[start short rant here...] By the way, yes, I could just set the Cell
Format
for everything to "Text" but then I'd have no way to check if a "text"
"4"
will be interpreted as a "number" 4 in my mail merge Word doc using ODBC
(and either changed to 4.0 or, even wose, just left blank!). And this all
wouldn't be a problem if I could do the mail merge using the default DDE
(which has worked completely fine on my computer for years now), but all
of
a sudden, Word started hanging when trying to open the mail merge base
documents using DDE. Aargh! And after much online searching and reading
of
newsgroups, I've only managed to determine that answers to *WHY* DDE mail
merges suddenly stop working and *HOW* on earth to fix them rank right up
there with what's the meaning of life and when will the Cubs win a World
Series again... [rant over]

Thanks for any help,
Karin
(using Office 2000, if that's important)

And here's my little macro:

Sub ChangeCellToText()
'
' ChangeCellToText Macro
' change cell format to text, then add space after number
'
Selection.NumberFormat = "@"

Dim CellText As String

CellText = ActiveCell.Text
CellText = CellText & " "
ActiveCell.Value = CellText

End Sub

 

[Guest]

Great to hear that it's working well for you. Thanks for the feedback.

Thanks so much. I'm using the second one and it works great!

Here's one that would do it and takes a 'hands-off' approach - it will
select
all cells on a sheet that were ever used (within the sheet's UsedRange
area)
and evaluate them. Since same cells may get evaluated more than once over
time, I added a check to keep from adding extra space characters to the
end
of an already converted number:

Sub NumToText()
Dim anyCell As Object
'this is probably overkill, but
'it's quick and sure
ActiveSheet.UsedRange.Select
For Each anyCell In Selection
If IsNumeric(anyCell.Value) Then
anyCell.NumberFormat = "@"
'keep from continually adding spaces
If Right(anyCell.Text, 1) <> " " Then
anyCell.Value = anyCell.Text & " "
End If
End If
Next
Range("A1").Select ' clean up appearance
End Sub

Now, if you'd rather choose the range of cells to test yourself, then
change
it to look like this and then select the cells to be tested before running
the code:
Sub NumToText()
Dim anyCell As Object
For Each anyCell In Selection
If IsNumeric(anyCell.Value) Then
anyCell.NumberFormat = "@"
'keep from continually adding spaces
If Right(anyCell.Text, 1) <> " " Then
anyCell.Value = anyCell.Text & " "
End If
End If
Next
Range("A1").Select ' clean up appearance
End Sub

Hi all--

I'd like to be able to select a range of cells, search that range for any
cells that contain a number (format is set to "General"), and then, if a
cell contains a number, change the Format to "Text" and then add a space
after the number.

Currently, I have a conditional format set to "=ISNUMBER(cellref)", which
highlights the cell in red if true. Then I have a short little
macro--listed
at the very end--that changes the Format and then adds the space. (Note:
all
I know about vba is what I've looked at after recording a macro and what
I've read or found online... so I'm pretty proud that I could do at least
this much!) But I soon tired of clicking on each cell (highlighted by
cond.
formt) and then clicking my macro button, so that got me wishing I could
just select the entire range and check it all at once. But that's totally
beyond my current vba capabilities.

[start short rant here...] By the way, yes, I could just set the Cell
Format
for everything to "Text" but then I'd have no way to check if a "text"
"4"
will be interpreted as a "number" 4 in my mail merge Word doc using ODBC
(and either changed to 4.0 or, even wose, just left blank!). And this all
wouldn't be a problem if I could do the mail merge using the default DDE
(which has worked completely fine on my computer for years now), but all
of
a sudden, Word started hanging when trying to open the mail merge base
documents using DDE. Aargh! And after much online searching and reading
of
newsgroups, I've only managed to determine that answers to *WHY* DDE mail
merges suddenly stop working and *HOW* on earth to fix them rank right up
there with what's the meaning of life and when will the Cubs win a World
Series again... [rant over]

Thanks for any help,
Karin
(using Office 2000, if that's important)

And here's my little macro:

Sub ChangeCellToText()
'
' ChangeCellToText Macro
' change cell format to text, then add space after number
'
Selection.NumberFormat = "@"

Dim CellText As String

CellText = ActiveCell.Text
CellText = CellText & " "
ActiveCell.Value = CellText

End Sub