Find/replace Hypens without changing to number formatting

  • Thread starter Thread starter Lee Matott
  • Start date Start date
L

Lee Matott

I am trying to remove the hypens that occur in
International Standard Book Numbers (ISBN). They are
importing is a format that includes (in a large
percentage of cases) a starting "0".
Samples of a few are shown below:
0-8133-3430-6
0-19-521910-4
0-534-60147-2
0-14-044333-9
0-375-70123-0
0-534-39771-9
I have to end up with the entries that look like this:
0813334306
0195219104
0534601472
0140443339
0375701230
0534397719
Some of the thousands of entries that I may have to
perform this operation, contain letters such as -X and
leading numbers other that "0".
Although Excel posts a potential workaround, it does not
work for this problem. They acknowledge it is a problem.
Can anybody suggest any solution?????
 
You could try selecting you data, then:
<Data> <Text To Columns>
<Delimited> <Next>
Check "other" and add the hyphen
Then <Finish>.

You now have four columns of numbers without hyphens.
In E1, enter this formula:

=A1&B1&C1&D1

And drag down to copy as needed.

If you have a large number of rows, after you enter the formula in E1, you
can simply select E1, and *double click* on the "fill handle".

This will *automatically* copy the formula down column E, as far as there's
data in column D.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I am trying to remove the hypens that occur in
International Standard Book Numbers (ISBN). They are
importing is a format that includes (in a large
percentage of cases) a starting "0".
Samples of a few are shown below:
0-8133-3430-6
0-19-521910-4
0-534-60147-2
0-14-044333-9
0-375-70123-0
0-534-39771-9
I have to end up with the entries that look like this:
0813334306
0195219104
0534601472
0140443339
0375701230
0534397719
Some of the thousands of entries that I may have to
perform this operation, contain letters such as -X and
leading numbers other that "0".
Although Excel posts a potential workaround, it does not
work for this problem. They acknowledge it is a problem.
Can anybody suggest any solution?????
 
Hi Lee,

Assuming that your column of ISBN numbers start at A1, put this formula
in B1 and copy down:

=SUBSTITUTE(A1,"-","")

Then,

1) Select Column B
2) Edit > Copy
3) Edit > Paste Special > Values > Click Ok
4) Delete Column A

Hope this helps!
 
Lee Matott said:
I am trying to remove the hypens that occur in
International Standard Book Numbers (ISBN). They are
importing is a format that includes (in a large
percentage of cases) a starting "0".
Samples of a few are shown below:
0-8133-3430-6
0-19-521910-4
0-534-60147-2
0-14-044333-9
0-375-70123-0
0-534-39771-9
I have to end up with the entries that look like this:
0813334306
0195219104
0534601472
0140443339
0375701230
0534397719
Some of the thousands of entries that I may have to
perform this operation, contain letters such as -X and
leading numbers other that "0".
Although Excel posts a potential workaround, it does not
work for this problem. They acknowledge it is a problem.
Can anybody suggest any solution?????
Click to expand...

The non-formula approach. Enter a single quote/apostrophe in a blank cell.
Copy that cell. Select the range containing your ISBNs. Run the menu command
Edit > Paste Special, choose Add in the dialog then click OK. This will add
an initial single quote to all the cells in the ISBN range. Now run Edit >
Replace, replacing hyphen with nothing. Clear the original cell containing
the single quote.
 
Back
Top