Find/Replace Wildcards - Excel 97

[JenW73]

Hi, all -

This is a weird question, I know. I fully expected to be able to do it and, to my chagrin, realized today that Excel is not as "sophisticated" as Word in the Find/Replace category.

I have a 13-tabbed spreadsheet that contains 8-9 digit numbers as part of the data in each cell. There are hundreds (if not thousands) of these cells in the document. Unfortunately for me, it seems that having these numbers bolded would be helpful for the folks that have to read it in hard copy. They are currently unbolded, as the rest of the information is in the cell.

Each number is different, so I want to find for the Excel equivalent of "^#^#^#^#^#^#^#^#" wildcard and replace with "Find What Text" in bold. Of course, that is completely possible in Word. I would normally look for "any" 8 digit number and replace it with "find what" in bold.

ARGG. Excel does not allow for wildcards in the "replace" part of the dialog. Is there any way around this? I've figured out how to isolate it in the "find" portion, but that is as far as I've been able to get.

The thought of manually bolding each of these numbers is giving me a headache so I NEED to try to find a way to do a mass find/replace, if possible, that won't compromise the integrity of the information!

Any help you could provide would be greatly appreciated.
JW

 

[euskadi]

So are you saying that there are cells containing gobs of text, with 8 digit numbers, like this:

"fajkfj ahlijf htgioasf 12345678" (for example) and you want just the 8 digit number in bold?

Or are you saying that you have a spreadsheet with some cells containing numbers (just numbers), and you want the cells with 8-digit numbers to be bold?

 

[Dreamboat]

I am shocked that you have a spreadsheet with all these *like* numbers but they aren't simply in columns or rows where you can choose the whole thing.

I would begin with getting the spreadsheet set up properly.

Sorry. It's probably not what you want to hear. Have you tried copy to Word, do the F/R and copy back? I'm not sure if it will retain the bold.

http://www.TheWordExpert.com

 

[Dreamboat]

Yep. It retains the bold.

http://www.TheWordExpert.com

 

[JenW73]

Hi, all -

Thanks for your responses. Sorry about that, it would seem that i didn't explain myself thoroughly.

The spreadsheet is actually a television program "log". Each cell contains integral information - program title, time, date andsomething called a house number (the 8 digit number in question). All of these pieces of information are inexorably linked and must appear in the same cell - placing them into separate columns would not do at all.

The rows refer to time of day and the columns are day of the week - Monday thru Sunday. Each cell, then, refers to the airing information for a particular hour in a particular day. Don't be shocked! This format is something that must accomplish many things - not the least of which, of course, is the ability for the folks over here to quickly see which programs are scheduled for a particular day.

So, euskadi, yes - you're absolutely correct. The information in each cell is part text and part number.

Dreamboat, yes I considered that. The problem is that we maintain change information in "comment" boxes and, as you know, these things don't transfer over well when copying/pasting between Word and Excel.

I guess what I'm mainly asking for, if it even exists, is the Excel version of ^& in Word - or the "Find What Text". If I can replace each number with itself in bold, my problems would be solved.

 

[AlaskanDad]

I had fun with this one!

Please try this out only on a copy of your information. It works in my tests but I don't know all of your details. The worst that will happen is nothing or it gets locked up.

Please let me know how this turns out.

Rob


Assumptions:
The 8 digit numbers have spaces on either side of them. It won't work for numbers at the beginning or ending of the text.
There may be up to 5 8 digit numbers in the text at a time. I have this thing set up for that many iterations but you can alter it.



Sub BoldTheEight()
On Error Resume Next

Dim a As Integer
Dim Iterations As Integer
Dim StartNum As Integer
Dim MaxRow As Integer
Dim MaxCol As Integer
Dim RowNum As Integer
Dim ColNum As Integer


Iterations = 5 'There may be up to five 8 digit numbers in each cell.

MaxRow = 4 'Change this to reflect the max number of rows you have
MaxCol = 4 'Change this to reflect the max number of columns you have

For a = 1 To Iterations

For RowNum = 1 To MaxRow
For ColNum = 1 To MaxCol

Cells(RowNum, ColNum).Activate
StartNum = 1

FoundABold: 'Only used if a bold 8 digit word was found in the text already

If IsError(WorksheetFunction.Search(" ???????? ", ActiveCell, StartNum)) = True Then
Resume DidntFindAny 'There aren't any 8 digit words in this text
Else

StartNum = WorksheetFunction.Search(" ???????? ", ActiveCell, StartNum) + 1
Rethink: 'Only used if the 8 digit word is non-numeric
If ActiveCell.Characters(Start:=StartNum, Length:=1).Font.FontStyle = "Bold" Then


StartNum = StartNum + 8
StartNum = WorksheetFunction.Search(" ???????? ", ActiveCell, StartNum)

GoTo FoundABold
End If
If ActiveCell.Characters(Start:=StartNum, Length:=1).Text > 9 Then
StartNum = WorksheetFunction.Search(" ???????? ", ActiveCell, StartNum) + 1
GoTo Rethink
Else

ActiveCell.Characters(Start:=StartNum, Length:=8).Font.FontStyle = "Bold"
End If
End If

DidntFindAny:

Next ColNum
Next RowNum

Next a

End Sub

 

[JenW73]

Hi there!

AlaskanDad, I can TOTALLY see where you're going with it and it's exactly what I was asking for except that it did lock the worksheet.

Basically want to find "any 8 numbers" and replace with THOSE numbers in bold.

So, bottom line is that the argument and logic seems ideal but it causing excel to breakdown. Any idea why?

thanks again for trying.

JW

 

[AlaskanDad]

You might want to try stepping through the code (F8) to see where you get caught up. It sounds like you're caught in some sort of loop.

I got caught in a couple of loops myself but fixed the code according to the sample data I had.

My code did find any 8 numbers and bolded only those characters.

Could you post one cell's worth of sample text so I can see what we're dealing with?

 

[JenW73]

No problem, here's an example of an 11:00am program cell:

11:00 AM On Assignment: 9603 Wolves of the Air; Kenya, Cradle of Champions; J - Wedding Dress Madness (8000002)

Basically 4 different fields of information. Time, series title, episode title and house number.

No hard returns or tabs. The separators are just spaces for ease of read. The number for which I want to search is in () and of course can be a string of 7 or 8 digits.

I've tweaked it a little as I was writing this response and it does, now, bold characters but it seems to be looking for any 7 or 8 strings with numbers contained. In other words, the time information above is bold along with the "AM", the space and the following letter.

It's not locking it up now!

 

[AlaskanDad]

Are all of your 8 digit numbers enclosed in parentheses? That would make this code just about fool-proof.

 

[JenW73]

Yep they are!

 

[AlaskanDad]

I'll give you something in just about an hour...

 

[AlaskanDad]

The code snippet you gave me had a seven digit number in it.
My code only works on 8 digit numbers.

Let me know how it goes.

Rob

Here it is:

Sub BoldTheEight()
On Error Resume Next

Dim a As Integer
Dim Iterations As Integer
Dim StartNum As Integer
Dim MaxRow As Integer
Dim MaxCol As Integer
Dim RowNum As Integer
Dim ColNum As Integer


Iterations = 5 'There may be up to five numbers in each text box.

MaxRow = 4
MaxCol = 4

For a = 1 To Iterations

For RowNum = 1 To MaxRow
For ColNum = 1 To MaxCol

Cells(RowNum, ColNum).Activate
StartNum = 1

FoundABold: 'Only used if a bold 8 digit word was found in the text already

If IsError(WorksheetFunction.Search("(????????)", ActiveCell, StartNum)) = True Then
Resume DidntFindAny 'There aren't any 8 digit words in this text
Else

StartNum = WorksheetFunction.Search("(????????)", ActiveCell, StartNum) + 1
Rethink: 'Only used if the 8 digit word is non-numeric
If ActiveCell.Characters(Start:=StartNum, Length:=1).Font.FontStyle = "Bold" Then

StartNum = StartNum + 8
StartNum = WorksheetFunction.Search("(????????)", ActiveCell, StartNum)

GoTo FoundABold
End If
If ActiveCell.Characters(Start:=StartNum, Length:=1).Text > 9 Then
StartNum = WorksheetFunction.Search("(????????)", ActiveCell, StartNum) + 1
GoTo Rethink
Else

ActiveCell.Characters(Start:=StartNum, Length:=8).Font.FontStyle = "Bold"
End If
End If

DidntFindAny:

Next ColNum
Next RowNum

Next a

End Sub