• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 496
  • Last Modified:

Replace a character in Excel Spreadsheet cell

I have a Parts List with over 2,000 part numbers - one part number per row and all in Column B.  The format of the part numbers is as follows:     9999-999-999

The software that I need to import the parts list into does not recognize the "-" (hyphen) embedded in the part number.  The part number format that MUST be adhered to in the new software requires a "space" where the "-" (hyphen) is now shown.

So, the newly formatted part number should look like this:      9999 999 999

Is there a way to make this replacement for all part numbers listed in the spreadsheet at one time?
  • 2
2 Solutions
Hi baleman2,

There sure is. Highlight/select column B, and press control-H to open the find/replace dialog. Enter a hyphen for the "find what", and a space for the "replace with" fields.

If you want it to only replace hyphens when the cell has a format of 9999-999-999, that can still be done but we'd have to write a macro for you to do it (easy enough). If needed, just let us know.

[ fanpages ]IT Services ConsultantCommented:
[edit] What Matt said! :) [/edit]


Select column [ B ], & then use the [CTRL]+[H] key combination to display the "Find and Replace" dialog box.

Enter a hyphen ("-" without the quotes) into the "Find what:" field.
Clear the "Replace with:" field if it contains anything already & enter a single space character (" ").

Click the [Options >>>] button & ensure that the following options are set:


"By Columns"

Look in:

[ ] Match case (not checked/ticked/selected)
[ ] Match entire cell contents (not checked/ticked/select)

Click the [Replace All] button.

That should be it!


baleman2Author Commented:
Thanks to you both.  I'm splitting the points equally because both solutions essentially gave me the same info and both were correct.
[ fanpages ]IT Services ConsultantCommented:
No problem at all.  I'm sure Matt won't mind.

Thanks in any respect & good luck with the rest of your project.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now