Wildcards

[Joe]

I am trying to find and replace a bunch of ISBN numbers in
an excel document using wildcards. I can find the
occurrences, but I do not know what to put in the replace
field. I have to find numbers like this:

0-13-0197742
0-13-0197750
0-13-0197769
0-13-0197777

and add a dash before the last number, so they would look
like this:

0-13-019774-2
0-13-019775-0
0-13-019776-9
0-13-019777-7

Thanks

 

[Andy B]

Hi

I don't think this can be done with Find/Replace. You could use a helper
column, alongside your existing data. If the data is unifrom throughout you
could try:
=IF(MID(A2,12,1)<>"-",LEFT(A2,11)&"-"&RIGHT(A2,1),A2)

This checks if the 12th digit is a - and if it isn't it recreates the data
with one inserted.
Once you have done this and are happy that it is right, fix these values by
selecting the range, Copy and then Paste / Special / Values.

 

[Andy Brown]

I'm informed that they don't necessarily have "-"s, so you need to be
careful.

Perhaps you could try code, along the lines of

For Each Cell In Selection
If Left(Cell, 2) = "0-" Then
Cell.Value = Left(Cell, 11) & "-" & Right(Cell, 1)
End If
Next

HTH,
Andy

 

[Guest]

Where would I enter this code? Do I enter it as a formula
or in the search and replace box?

Thanks

 

[Andy Brown]

Where would I enter this code? Do I enter it as a formula ...

VBA (Visual Basic for Applications) code (AKA macros) goes in a code module.

Press ALT+F11 to access the V(isual) B(asic) E(ditor). Choose Module from
the Insert menu & paste in, e.g.

-----------------------------------------------------------
Sub ISBN()
For Each Cell In Selection
If Left(Cell, 2) = "0-" Then
Cell.Value = Left(Cell, 11) & "-" & Right(Cell, 1)
End If
Next
End Sub
------------------------------------------------------------

ALT+Q to quit the VBE. Save the file (in case the code screws up), then
select your range to check and ALT+F8 then doubleclick the macro name to run
the code.

Rgds,
Andy

 

[Dave Peterson]

Maybe a formula in a helper column like:

=TEXT(--SUBSTITUTE(A1,"-",""),"0-00-000000-0")